Filtering and blinking problem

[sjh]

Hi,
I have two sheets in my Excel workbook - sheet1 and sheet2. In sheet1, there's an auto filter. For example, the Season column can be filtered into Spring, Summer, etc. Once the column is filtered, the user can modify the data related to this column

There's a Reset button on the sheet1, so that when user clicks on this button, it copies the original data from sheet2 to sheet1. If this button is cliked after the filtering is done, the worksheet "blinks" each time it copies a row from sheet2 to sheet1. Meaning that the copy operation is visible to the user.

Is there a way to prevent this blinking behavior?

Thank you.

 

[sjh]

Hi, I have another related question. After the filtering, some rows get hidden from the view. Is there a way to refer only to the visible rows on the sheet?

Thank you!

 

[michaelbr55]

If you add "Application.ScreenUpdating = False" at the beginning of your code and then set it back to True at the end that should stop it blinking.
As to your other question how do you hide the rows? and how do you want to refer to the one's still visible?



Mike

 

[sjh]

Thanks Mike for your post! It stopped blinking!

And about referring only the visible rows after filtering:
I wanted to do something like
For i=startVisibleRow to endVisibleRow
'do stuff
Next i
but, I got around this problem by checking for the currently filtered value. But if there's an easy way of referring the visible rows, it would be nice to know.

Thank you again!

 

[xlbo]

this should get you going

for each c in range("YourRange&quot.specialcells(xlcelltypevisible)
msgbox c.value
next c

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244