Macro to hide blank rows in Workbook

[bobby_793]

Halloo

I have 15 work sheets and i put a VB code to hide the blank rows or the row if no value values. That code works fine. But the matter is i have 15 macros. i want my this code should run with all 15 sheet instead of having the 15 macros. Code is below.


Sub HideRowIfZeroInQ()
Dim R As Range
Dim LastRow As Long
LastRow = Cells(Rows.Count, "O").End(xlUp).Row
If LastRow > 800 Then LastRow = 800
For Each R In Range("O9:O" & CStr(LastRow))
If R.Value = 0 And R.Value <> "Q" Then R.EntireRow.Hidden = True
Next

End Sub


Note :- When i run this code its work fine with me but very slow, if some one add some feature to make it fast

Thanks for help

[mikerickson]

Sub HideZeroQAllSheets()
Dim oneSheet As Worksheet
Application.ScreenUpdating = False
For Each oneSheet In ThisWorkbook.WorkSheets
Call HideRowIfZeroQ(oneSheet)
Next oneSheet
Application.ScreenUpdating = True
End Sub

Sub HideRowIfZeroQ(oneWorksheet As Worksheet)
Dim R As Range, lastrow As Long
With oneWorksheet.Range("o1:o800")
For Each R In Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
R.EntireRow.Hidden = (CStr(R.Value) = "0")
Next R
End With
End Sub

[bobby_793]

thank you mikerickson

[mdmackillop]

Hi Bobby,
If this is Soved you can mark it so using the Thread Tools dropdown

[mdmackillop]

Make this change to avoid an error if you have any chart sheets

For Each oneSheet In ThisWorkbook.Worksheets

[bobby_793]

mikerickson i don't have any chart sheet in this work sheet

[mdmackillop]

i don't have any chart sheet in this work sheet
Perhaps not, but others may, who make use of this solution.

[mikerickson]

Ok tell me if i can attache this code with button in work sheet
Getting a command buttons from the Forms Menu and assigning it to this macro would be one way to activate the routine.

With all those rows hiding, the Properties of the command Buttons should be set to "Don't move or size with cells".

Since it works across different worksheets, you might want to use Tools>Customize>Command bars to add a Macro button to the command bar so you can activate this from any sheet.

mdmackillop, good catch.

[bobby_793]

Dear mikerickson


Thank you for your help, while running in code can i show a msg box in between "pls Wait while Processing". when code complete then "successfully Done".





Sub HideZeroQAllSheets()
Dim oneSheet As Worksheet
Application.ScreenUpdating = False
For Each oneSheet In ThisWorkbook.WorkSheets
Call HideRowIfZeroQ(oneSheet)
Next oneSheet
Application.ScreenUpdating = True
End Sub


Sub HideRowIfZeroQ(oneWorksheet As Worksheet)
Dim R As Range, lastrow As Long
With oneWorksheet.Range("o1:o800")
For Each R In Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
R.EntireRow.Hidden = (CStr(R.Value) = "0")
Next R
End With
End Sub

[Simon Lloyd]

Bobby_793, when pasting code please be sure to use the code tags as i have done on your thread, this is done by highlighting the code and clicking the Green VBA square in at the top of your new post window.

[mikerickson]

A MsgBox "Please wait while processing" would delay start of the processing until the user dismissed the box.

A MsgBox "Done" requires that a useless key be pressed.

If one were to put them in, it would be before setting the ScreenUpdating to False and after it were reset to True.

[mdmackillop]

You could do this with a userform. Add a userform with a label and try

Sub HideZeroQAllSheets()
Dim oneSheet As Worksheet
UserForm1.Show False
UserForm1.Label1.Caption = "Please wait"
Application.ScreenUpdating = False
For Each oneSheet In ThisWorkbook.Worksheets
Call HideRowIfZeroQ(oneSheet)
Next oneSheet
Application.ScreenUpdating = True
UserForm1.Label1.Caption = "Finished"
tim = Timer
Do Until Timer > tim + 2
DoEvents
Loop
Unload UserForm1
End Sub

[mikerickson]

mdmackillop,

Sweet!

I tend to overlook modeless userforms.