How to hide rows in a Workbook with multiple sheets with zero valu

[khurram_razaq]

Hi,

I am trying to create a code to run on a workbook with several work sheets
to hide rows with zero values.

I have used the following code:


Sub HideRowsIfColumnDisEmpty()
Dim X As Long
Dim LastRowOfData As Long
With Worksheets("Functional SummaryTotal Risk")
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 With
End Sub

This works for one worksheet in a workbook, however I need this to work on
multiple sheets.

Could someone please help me on how I can modify this could so that it can
run successfully on multipule worksheets in a workbook.

Thank you.

Khurram

 

[Joel]

Sub HideRowsIfColumnDisEmpty()
Dim X As Long
Dim LastRowOfData As Long
for each sht in Thisworkbook.sheets
With sht
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 X
End With
Next sht
End Sub

 

[Per Jessen]

Hi Khurram

The modified code below will loop through all sheets in the workbook.

If you only want to loop through some sheets in the workbook you need
another apporach.

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

Regards,
Per

 

[khurram_razaq]

Thank you for your help.

Sub HideRowsIfColumnDisEmpty()
Dim X As Long
Dim LastRowOfData As Long
for each sht in Thisworkbook.sheets
With sht
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 X
End With
Next sht
End Sub

 

[khurram_razaq]

Thank you for your help.

Hi Khurram

The modified code below will loop through all sheets in the workbook.

If you only want to loop through some sheets in the workbook you need
another apporach.

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

Regards,
Per

 

[khurram_razaq]

Hi Per,

Thanks for your advice yesterday.

When I use the code you provided, it doesnot hide the rows on all the work
sheets.
Secondly, for example if there are 4 works 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?

Thanks

Khurram

 

[Per Jessen]

Hi Khurram

Thanks for your reply.

The code should hide rows on all worksheets. Don't know why it doesn't
happen.

The code below will skip "sheet" and hide rows on other sheets.

Sub HideRowsIfColumnDisEmpty()
Dim sh As Long
Dim X As Long
Dim LastRowOfData As Long
For sh = 1 To Sheets.Count
If Sheets(sh).Name = "Sheet" Then GoTo Resume_Next
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
Resume_Next:
Next
Next
End Sub

Regards,
Per