ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Hide rows with zero's on several sheets within a workbook (https://www.excelbanter.com/excel-programming/412176-hide-rows-zeros-several-sheets-within-workbook.html)

khurram_razaq
Hide rows with zero's on several sheets within a workbook
 
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

[email protected]
Hide rows with zero's on several sheets within a workbook
 
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

On Jun 6, 9:52*am, khurram_razaq
wrote:
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

Mike H
Hide rows with zero's on several sheets within a workbook
 
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

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

khurram_razaq
Hide rows with zero's on several sheets within a workbook
 
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



" wrote:

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

On Jun 6, 9:52 am, khurram_razaq
wrote:
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

[email protected]
Hide rows with zero's on several sheets within a workbook
 
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

On Jun 6, 10:26*am, khurram_razaq
wrote:
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



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

On Jun 6, 9:52 am, khurram_razaq
wrote:
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- Hide quoted text -

- Show quoted text -

Mike H
Hide rows with zero's on several sheets within a workbook
 
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" wrote:

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



" wrote:

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

On Jun 6, 9:52 am, khurram_razaq
wrote:
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

khurram_razaq
Hide rows with zero's on several sheets within a workbook
 
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

" wrote:

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

On Jun 6, 10:26 am, khurram_razaq
wrote:
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



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

On Jun 6, 9:52 am, khurram_razaq
wrote:
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- Hide quoted text -

- Show quoted text -

khurram_razaq
Hide rows with zero's on several sheets within a workbook
 
Hi Mike,

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

Any suggestions?

Khurram

"Mike H" wrote:

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

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



" wrote:

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

On Jun 6, 9:52 am, khurram_razaq
wrote:
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

[email protected]
Hide rows with zero's on several sheets within a workbook
 
Hi
Sorry, it should be
Dim sh As String

I was mixing up numbers and names!
Paul

On Jun 6, 11:07*am, khurram_razaq
wrote:
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



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

On Jun 6, 10:26 am, khurram_razaq
wrote:
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

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

On Jun 6, 9:52 am, khurram_razaq
wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Mike H
Hide rows with zero's on several sheets within a workbook
 
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



"khurram_razaq" wrote:

Hi Mike,

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

Any suggestions?

Khurram

"Mike H" wrote:

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

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



" wrote:

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

On Jun 6, 9:52 am, khurram_razaq
wrote:
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

Mike H
Hide rows with zero's on several sheets within a workbook
 
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" wrote:

Hi Mike,

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

Any suggestions?

Khurram

"Mike H" wrote:

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

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



" wrote:

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

On Jun 6, 9:52 am, khurram_razaq
wrote:
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

khurram_razaq
Hide rows with zero's on several sheets within a workbook
 
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

"Mike H" wrote:

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

Hi Mike,

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

Any suggestions?

Khurram

"Mike H" wrote:

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

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



" wrote:

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

On Jun 6, 9:52 am, khurram_razaq
wrote:
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


All times are GMT +1. The time now is 08:41 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com