hide row based on cell value in multiple sheets

**fatpiggy123** · 05-13-2010, 04:08 AM

Hi all,

I have this macro that hides rows when the value is “0”.

At the moment it’s not running very smoothly and updating quickly, i.e the Application.Sreenupdating isn’t working correctly.

I would also like to add run this in multiple sheets at the same time. Is this also possible?

Sub HideRows()

Dim cell As Range

Application.ScreenUpdating = False

For Each cell In Range("AU6:AU100")
cell.EntireRow.Hidden = cell.Value = 0

Application.ScreenUpdating = True

Next cell

End Sub

Any help appreciated.

Thanks in advance,

Mike

**Richard Buttrey** · 05-13-2010, 04:20 AM

Hi Mike,

Put the Application.ScreenUpdating = True after the Next Cell instruction.

However can I also suggest an improvement. In general if you can avoid looping through cells by using another method then you should do so. Every pass through the loop requires the VBE to jump back to the Application, do something then jump back to the VBE. There is a time overhead every time this happens.

My suggestion would be to use the macro to perform a standard Excel autofilter so that you custom filter the range for values that do not equal zero,
This will be far quicker, especially with large ranges.

Regards

**dominicb** · 05-13-2010, 04:21 AM

Come on fatpiggy123

We've been through all this before. What have you not done?
Post again and let's get it right this time. Thread locked.

DominicB

hide row based on cell value in multiple sheets

LinkBack

Thread Tools

Display

hide row based on cell value in multiple sheets

Re: hide row based on cell value in multiple sheets

Re: hide row based on cell value in multiple sheets

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions