| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 - |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 - |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 - |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to hide rows in a Workbook with multiple sheets with zero valu | Excel Programming | |||
| Hide all Sheets in Workbook then Close Workbook | Excel Programming | |||
| hide non active sheets in workbook | Excel Programming | |||
| why can't hide zero's based on a zero valuer in range | Excel Programming | |||
| Hide Rows Across Mulitple Sheets | Excel Programming | |||
Linear Mode
