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

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" wrote:

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

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" skrev i meddelelsen
...
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

Thank you for your help.

"Joel" wrote:


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" wrote:

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

Thank you for your help.

"Per Jessen" wrote:

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" skrev i meddelelsen
...
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

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" wrote:

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" skrev i meddelelsen
...
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

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

"khurram_razaq" skrev i meddelelsen
...
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" wrote:

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" skrev i
meddelelsen
...
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