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

K

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
 
P

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
 
M

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
 
K

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
 
P

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
 
M

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
 
K

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
 
K

khurram_razaq

Hi Mike,

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

Any suggestions?

Khurram
 
P

paul.robinson

Hi
Sorry, it should be
Dim sh As String

I was mixing up numbers and names!
Paul
 
M

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
 
M

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
 
K

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top