Macro to hide roes in multiple sheets

[mike_ate_a_pie]

Hi all,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have this macro that hides rows when the value is “0”.

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

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

<o></o>
Sub HideRows()
<o></o>
Dim cell As Range
<o></o>
Application.ScreenUpdating = False
<o></o>
For Each cell In Range("AU6:AU100")
cell.EntireRow.Hidden = cell.Value = 0
<o></o>
Application.ScreenUpdating = True
<o></o>
Next cell
<o></o>
End Sub

<o></o>
<o></o>
Any help appreciated.

<o></o>
Thanks in advance,

<o></o>
Mike

 

[Norie]

Mike

Why are you turning on screen updating within the loop?

Putting this after Next cell might help with the first issue.

Application.ScreenUpdating = True

 

[mike_ate_a_pie]

i tried this but the way i currently have it you can see the code running which i prefer. When you move the screen updating to below it pauses while the code runs.

thanks

 

[John Davis]

i tried this but the way i currently have it you can see the code running which i prefer. When you move the screen updating to below it pauses while the code runs.

thanks

Just out of curiosity. Why would you turn the screen updating off in the first place, if you want to see the code running?

 

[Norie]

mike

I'm a little confused - you appear to want to turn off screen updating but you still want to see what's happening?

 

[Domenic]

Try...

Option Explicit

Sub HideRows()

    Dim Wksht As Worksheet
    Dim cell As Range
    
    For Each Wksht In Worksheets
        For Each cell In Wksht.Range("AU6:AU100")
            cell.EntireRow.Hidden = cell.Value = 0
        Next cell
    Next Wksht

End Sub

 

[mike_ate_a_pie]

i'm not bothered about the watching the code run but i want this the macro to be manually run. so you can chose to hide rows or view them again. currently i just have the macro above and then a sepearte macro just to unhide the lines.

I would like to run it in around 10 of my 20 worksheets as these are all the same.

Currently i can only run it in one spreadsheet at once and it as before it has the delay.

hope this makes a little more sense... maybe

thanks

 

[Domenic]

Try...

Option Explicit

Sub HideRows()

    Dim SheetNames As Variant
    Dim sname As Variant
    Dim cell As Range

[COLOR="SeaGreen"]'   Change the sheet names, accordingly[/COLOR]
    SheetNames = Array("Sheet1", "Sheet2", "Sheet3", ..... "Sheet10")
    
    For Each sname In SheetNames
        For Each cell In Sheets(sname).Range("AU6:AU100")
            cell.EntireRow.Hidden = (cell.Value = 0) And Not cell.EntireRow.Hidden
        Next cell
    Next sname

End Sub