Hide rows with zero's on several sheets within a workbook

[khurram_razaq]

Hi,

I recently got some help on getting a VBA code to hide rows with zero.
However what I want to do is modilfy it so that I can run it only on specific
sheets within a workbook. For example if there are 4 work sheet, sheet1,
sheet2, sheet3 and
sheet4 and I want one marco but want to apply it to sheet 1, sheet3 and sheet
4 only what changes will I need to make to the code below?

Sub HideRowsIfColumnDisEmpty()
Dim sh As Long
Dim X As Long
Dim LastRowOfData As Long
For sh = 1 To Sheets.Count
Sheets(sh).Activate
LastRowOfData = Cells(Rows.Count, "N").End(xlUp).Row
For X = 1 To LastRowOfData
If Cells(X, "N").Value = 0 Then
Cells(X, "N").EntireRow.Hidden = True
End If
Next
Next
End Sub

Thank you for your help.

Khurram

 

[paul.robinson]

Hi
Try this. not tested.

Sub HideRowsIfColumnDisEmpty()
Dim sh As Long
Dim X As Long
Dim SheetNumbers as Variant
Dim LastRowOfData As Long

SheetNumbers = Array(1,3,4)
For sh = SheetNumbers(1) To SheetNumbers(3)
Sheets(sh).Activate
LastRowOfData = Cells(Rows.Count, "N").End(xlUp).Row
For X = 1 To LastRowOfData
If Cells(X, "N").Value = 0 Then
Cells(X, "N").EntireRow.Hidden = True
End If
Next
Next
End Sub

regards
Paul

 

[Mike H]

Try this

Sub HideRowsIfColumnDisEmpty()
Dim sh As Long
Dim X As Long
Dim LastRowOfData As Long
For sh = 1 To Sheets.Count
If sh = 2 Then sh = sh + 1
Sheets(sh).Activate
LastRowOfData = Cells(Rows.Count, "N").End(xlUp).Row
For X = 1 To LastRowOfData
If Cells(X, "N").Value = 0 Then
Cells(X, "N").EntireRow.Hidden = True
End If
Next
Next
End Sub

Mike

:
M

 

[khurram_razaq]

Hi,

I tried to use the below code with some changes but get a error message,
"Subscript out of range".

Sub HideRowsIfColumnDisEmpty()
Dim sh As Long
Dim X As Long
Dim SheetNumbers As Variant
Dim LastRowOfData As Long

SheetNumbers = Array(FunctionalSummaryTotalRisk, FunctionalSummaryTotalFinanc)
For sh = SheetNumbers(1) To SheetNumbers(2)
Sheets(sh).Activate
LastRowOfData = Cells(Rows.Count, "N").End(xlUp).Row
For X = 1 To LastRowOfData
If Cells(X, "N").Value = 0 Then
Cells(X, "N").EntireRow.Hidden = True
End If
Next
Next
End Sub

Basically there are a total of 12 worksheets in the workbook and I only want
ot apply this to 2 which are FunctionalSummaryTotalRisk,
FunctionalSummaryTotalFinanc.

Please help.
Thanks.

Khurram

 

[paul.robinson]

Hi
Your original mail suggested you wanted to use sheet numbers, but now
you want to use sheet names. Try this

Sub HideRowsIfColumnDisEmpty()
Dim sh As Long
Dim X As Long
Dim SheetNames As Variant
Dim LastRowOfData As Long


SheetNames = Array("FunctionalSummaryTotalRisk",
"FunctionalSummaryTotalFinanc")
For sh = SheetNames(1) To SheetNames(2)
Sheets(sh).Activate
LastRowOfData = Cells(Rows.Count, "N").End(xlUp).Row
For X = 1 To LastRowOfData
If Cells(X, "N").Value = 0 Then
Cells(X, "N").EntireRow.Hidden = True
End If
Next
Next
End Sub

Again, not tested.
regards
Paul

 

[Mike H]

Try this

Sub HideRowsIfColumnDisEmpty()
Dim sh As Long
Dim X As Long
Dim LastRowOfData As Long
For sh = 1 To Sheets.Count
If ActiveSheet.Name <> ("FunctionalSummaryTotalRisk") And
ActiveSheet.Name <> _
("FunctionalSummaryTotalFinanc") Then
LastRowOfData = Cells(Rows.Count, "N").End(xlUp).Row
For X = 1 To LastRowOfData
MsgBox ActiveSheet.Name
If Cells(X, "N").Value = 0 Then
Cells(X, "N").EntireRow.Hidden = True
End If
Next
End If
Next
End Sub

Mike

 

[khurram_razaq]

Hi Paul,

Thanks for this and apologies for the e-mail mis-communciation.

I the new code below but it doesnot work as I get a error message saying,
"Type mismatch".

Any suggestions as to why this is?

Thanks.

Khurram

 

[khurram_razaq]

Hi Mike,

I tired this but the code is not doing anything at all.

Any suggestions?

Khurram

 

[paul.robinson]

Hi
Sorry, it should be
Dim sh As String

I was mixing up numbers and names!
Paul

 

[Mike H]

Hi,

I never tested it to see if it did anything I assumed it did and simply
modified it as per your request. I've tested it now and for any sheet that
isn't named
"FunctionalSummaryTotalRisk" or
"FunctionalSummaryTotalFinanc"
It hides the entire row if column N is zero.

If it isn't doing that for you then have you got it in the correct place it
should be in a general module and not worksheet code. Alt + F11 to open VB
editor, Right click 'This workbook' and insert module and paste it in there.
I had left a degugging line in which is now removed but that wouldn't have
stopped it working.

If that doesn't work check your data, is it really a zero oin column N

Sub HideRowsIfColumnDisEmpty()
Dim sh As Long
Dim X As Long
Dim LastRowOfData As Long
For sh = 1 To Sheets.Count
If ActiveSheet.Name <> ("FunctionalSummaryTotalRisk") And
ActiveSheet.Name <> _
("FunctionalSummaryTotalFinanc") Then
LastRowOfData = Cells(Rows.Count, "N").End(xlUp).Row
For X = 1 To LastRowOfData

If Cells(X, "N").Value = 0 Then
Cells(X, "N").EntireRow.Hidden = True
End If
Next
End If
Next
End Sub

Mike

 

[Mike H]

My apologies, there was a bug in it. Corrected code

Sub HideRowsIfColumnDisEmpty()
Dim sh As Long
Dim X As Long
Dim LastRowOfData As Long
For sh = 1 To Sheets.Count
Sheets(sh).Select
If ActiveSheet.Name <> ("FunctionalSummaryTotalRisk") And
ActiveSheet.Name <> _
("FunctionalSummaryTotalFinanc") Then
LastRowOfData = Cells(Rows.Count, "N").End(xlUp).Row
For X = 1 To LastRowOfData
If Cells(X, "N").Value = 0 Then
Cells(X, "N").EntireRow.Hidden = True
End If
Next
End If
Next
End Sub

 

[khurram_razaq]

Hi Mike,

I tried this but now get a error saying Select Method of Worksheet class
failed.

And further t your other e-mailI have input the code in Module and all the
values i.e. 0 and non 0 are in column N in every single worksheet.

Totally getting confused.....as to why this is not working for me.

Any suggestions.

Thanks.

Khurram