Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide empty worksheets
Hi,
I have a workbook with worksheets created by an external ETL application. The end user needs to address data issues in some of the worksheets created. The workbook can get very wide, with many worksheets. I'd like to create macros to: * Hide_Empty_Worksheets An empty worksheet would only have a header row, i.e. row 1 contains Var1, Var2,...,VarN. No other data from row 2 and below. * Show_All_Worksheets Undo the action from above. I'd like to bind these macros to hotkeys, i.e. Cntl-Shift-H to hide and Cntl-Shift-S to show. Does anyone have similar macros that I could use, or point me in the right direction? Unfortunately I'm not an Excel macro guru, so if you're pointing me in the right direction, any helpful URL's for online resources to help with Excel programming would be useful. Thanks, Scott |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide empty worksheets
At least one sheet has to be visible in every workbook.
To avoid any error that could occur by hiding sheets in the wrong order, I'd create a sheet that would always be visible. Then make sure that this sheet stays visible. In my code to hide sheets, I used the name "Instructions". But you could the name you want. Option Explicit Sub HideSheets() Dim wks As Worksheet Dim InstWks As Worksheet Set InstWks = Worksheets("Instructions") InstWks.Visible = xlSheetVisible For Each wks In ActiveWorkbook.Worksheets With wks If .Name = InstWks.Name Then 'skip it Else If Application.CountA(.Rows("2:" & .Rows.Count)) = 0 Then 'hide it .Visible = xlSheetHidden Else .Visible = xlSheetVisible End If End If End With Next wks End Sub Sub ShowAllSheets() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets wks.Visible = xlSheetVisible Next wks End Sub You can use this to assign the shortcut keys: Tools|Macro|Macros|Select a macro and click the Options button assign your shortcut key preference and click ok Then click cancel Scott Bass wrote: Hi, I have a workbook with worksheets created by an external ETL application. The end user needs to address data issues in some of the worksheets created. The workbook can get very wide, with many worksheets. I'd like to create macros to: * Hide_Empty_Worksheets An empty worksheet would only have a header row, i.e. row 1 contains Var1, Var2,...,VarN. No other data from row 2 and below. * Show_All_Worksheets Undo the action from above. I'd like to bind these macros to hotkeys, i.e. Cntl-Shift-H to hide and Cntl-Shift-S to show. Does anyone have similar macros that I could use, or point me in the right direction? Unfortunately I'm not an Excel macro guru, so if you're pointing me in the right direction, any helpful URL's for online resources to help with Excel programming would be useful. Thanks, Scott -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide empty worksheets
Thanks Dave, these macros below work great.
I now have need for three (similar) macros... Say I have this worksheet: Var1 Var2 SomeData 1 0 A 0 1 B 1 1 C I need these macros: FilterData(): Turn on autofilter (if not already on) Filter where Var1=1 and Var2=0 (would return SomeData=A) ShowAllData(): Keep autofilter turned on, but show all rows HideSheets(): Call the FilterData() macro *** Hide the worksheet if the *filtered* data returns no rows (still have the header row) *** Or possibly just embed the FilterData criteria into the COUNTIF and/or DCOUNT function without turning on autofiltering for the end user. I've looked into the COUNTIF and DCOUNT functions, but can't quite work out the syntax. Any help appreciated. Thanks, Scott "Dave Peterson" wrote in message ... At least one sheet has to be visible in every workbook. To avoid any error that could occur by hiding sheets in the wrong order, I'd create a sheet that would always be visible. Then make sure that this sheet stays visible. In my code to hide sheets, I used the name "Instructions". But you could the name you want. Option Explicit Sub HideSheets() Dim wks As Worksheet Dim InstWks As Worksheet Set InstWks = Worksheets("Instructions") InstWks.Visible = xlSheetVisible For Each wks In ActiveWorkbook.Worksheets With wks If .Name = InstWks.Name Then 'skip it Else If Application.CountA(.Rows("2:" & .Rows.Count)) = 0 Then 'hide it .Visible = xlSheetHidden Else .Visible = xlSheetVisible End If End If End With Next wks End Sub Sub ShowAllSheets() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets wks.Visible = xlSheetVisible Next wks End Sub You can use this to assign the shortcut keys: Tools|Macro|Macros|Select a macro and click the Options button assign your shortcut key preference and click ok Then click cancel Scott Bass wrote: Hi, I have a workbook with worksheets created by an external ETL application. The end user needs to address data issues in some of the worksheets created. The workbook can get very wide, with many worksheets. I'd like to create macros to: * Hide_Empty_Worksheets An empty worksheet would only have a header row, i.e. row 1 contains Var1, Var2,...,VarN. No other data from row 2 and below. * Show_All_Worksheets Undo the action from above. I'd like to bind these macros to hotkeys, i.e. Cntl-Shift-H to hide and Cntl-Shift-S to show. Does anyone have similar macros that I could use, or point me in the right direction? Unfortunately I'm not an Excel macro guru, so if you're pointing me in the right direction, any helpful URL's for online resources to help with Excel programming would be useful. Thanks, Scott -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide empty worksheets
Record a macro when you apply the filter to your worksheet and you'll have the
code. Same thing when you show the data. As for hiding the sheet... If Application.CountA(.Rows("2:" & .Rows.Count)) = 0 Then 'hide it .Visible = xlSheetHidden Else .Visible = xlSheetVisible End If becomes If Application.CountA(.Rows("2:" & .Rows.Count)) = 0 _ or .autofilter.range.columns(1).cells _ .specialcells(xlcelltypevisible).count = 1 Then 'hide it .Visible = xlSheetHidden Else .Visible = xlSheetVisible End If This does expect that you'e already added those arrows to each sheet. Scott Bass wrote: Thanks Dave, these macros below work great. I now have need for three (similar) macros... Say I have this worksheet: Var1 Var2 SomeData 1 0 A 0 1 B 1 1 C I need these macros: FilterData(): Turn on autofilter (if not already on) Filter where Var1=1 and Var2=0 (would return SomeData=A) ShowAllData(): Keep autofilter turned on, but show all rows HideSheets(): Call the FilterData() macro *** Hide the worksheet if the *filtered* data returns no rows (still have the header row) *** Or possibly just embed the FilterData criteria into the COUNTIF and/or DCOUNT function without turning on autofiltering for the end user. I've looked into the COUNTIF and DCOUNT functions, but can't quite work out the syntax. Any help appreciated. Thanks, Scott "Dave Peterson" wrote in message ... At least one sheet has to be visible in every workbook. To avoid any error that could occur by hiding sheets in the wrong order, I'd create a sheet that would always be visible. Then make sure that this sheet stays visible. In my code to hide sheets, I used the name "Instructions". But you could the name you want. Option Explicit Sub HideSheets() Dim wks As Worksheet Dim InstWks As Worksheet Set InstWks = Worksheets("Instructions") InstWks.Visible = xlSheetVisible For Each wks In ActiveWorkbook.Worksheets With wks If .Name = InstWks.Name Then 'skip it Else If Application.CountA(.Rows("2:" & .Rows.Count)) = 0 Then 'hide it .Visible = xlSheetHidden Else .Visible = xlSheetVisible End If End If End With Next wks End Sub Sub ShowAllSheets() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets wks.Visible = xlSheetVisible Next wks End Sub You can use this to assign the shortcut keys: Tools|Macro|Macros|Select a macro and click the Options button assign your shortcut key preference and click ok Then click cancel Scott Bass wrote: Hi, I have a workbook with worksheets created by an external ETL application. The end user needs to address data issues in some of the worksheets created. The workbook can get very wide, with many worksheets. I'd like to create macros to: * Hide_Empty_Worksheets An empty worksheet would only have a header row, i.e. row 1 contains Var1, Var2,...,VarN. No other data from row 2 and below. * Show_All_Worksheets Undo the action from above. I'd like to bind these macros to hotkeys, i.e. Cntl-Shift-H to hide and Cntl-Shift-S to show. Does anyone have similar macros that I could use, or point me in the right direction? Unfortunately I'm not an Excel macro guru, so if you're pointing me in the right direction, any helpful URL's for online resources to help with Excel programming would be useful. Thanks, Scott -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide empty worksheets
Another way if you're using that data|filter|autofilter to hide the rows--and
you have something in column A: If Application.subtotal(3, .columns(1)) <= 1 Then 'hide it .Visible = xlSheetHidden Else .Visible = xlSheetVisible End If =subtotal(3,a:a) will count the number of visible cells in column a. If you're using xl2003+, you can use: If Application.subtotal(103, .columns(1)) <= 1 Then 103 will count the visible cells in column A no matter if you hid them manually or with autofilter. Scott Bass wrote: Thanks Dave, these macros below work great. I now have need for three (similar) macros... Say I have this worksheet: Var1 Var2 SomeData 1 0 A 0 1 B 1 1 C I need these macros: FilterData(): Turn on autofilter (if not already on) Filter where Var1=1 and Var2=0 (would return SomeData=A) ShowAllData(): Keep autofilter turned on, but show all rows HideSheets(): Call the FilterData() macro *** Hide the worksheet if the *filtered* data returns no rows (still have the header row) *** Or possibly just embed the FilterData criteria into the COUNTIF and/or DCOUNT function without turning on autofiltering for the end user. I've looked into the COUNTIF and DCOUNT functions, but can't quite work out the syntax. Any help appreciated. Thanks, Scott "Dave Peterson" wrote in message ... At least one sheet has to be visible in every workbook. To avoid any error that could occur by hiding sheets in the wrong order, I'd create a sheet that would always be visible. Then make sure that this sheet stays visible. In my code to hide sheets, I used the name "Instructions". But you could the name you want. Option Explicit Sub HideSheets() Dim wks As Worksheet Dim InstWks As Worksheet Set InstWks = Worksheets("Instructions") InstWks.Visible = xlSheetVisible For Each wks In ActiveWorkbook.Worksheets With wks If .Name = InstWks.Name Then 'skip it Else If Application.CountA(.Rows("2:" & .Rows.Count)) = 0 Then 'hide it .Visible = xlSheetHidden Else .Visible = xlSheetVisible End If End If End With Next wks End Sub Sub ShowAllSheets() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets wks.Visible = xlSheetVisible Next wks End Sub You can use this to assign the shortcut keys: Tools|Macro|Macros|Select a macro and click the Options button assign your shortcut key preference and click ok Then click cancel Scott Bass wrote: Hi, I have a workbook with worksheets created by an external ETL application. The end user needs to address data issues in some of the worksheets created. The workbook can get very wide, with many worksheets. I'd like to create macros to: * Hide_Empty_Worksheets An empty worksheet would only have a header row, i.e. row 1 contains Var1, Var2,...,VarN. No other data from row 2 and below. * Show_All_Worksheets Undo the action from above. I'd like to bind these macros to hotkeys, i.e. Cntl-Shift-H to hide and Cntl-Shift-S to show. Does anyone have similar macros that I could use, or point me in the right direction? Unfortunately I'm not an Excel macro guru, so if you're pointing me in the right direction, any helpful URL's for online resources to help with Excel programming would be useful. Thanks, Scott -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide empty worksheets
Oops, one more thing. My example was too simplistic.
Most worksheets will have this structure (simplified example): Var1 Var2 SomeData but some worksheets will have this structu InputTable Var1 Var2 SomeData I need to autofilter, hide sheets, etc. based on the column name (Var1 & Var2), rather than column position. Is there some way in Excel to tell it that row1 defines the "name" of the column, and reference the column by name in the macro? Thanks, Scott "Dave Peterson" wrote in message ... Record a macro when you apply the filter to your worksheet and you'll have the code. Same thing when you show the data. As for hiding the sheet... If Application.CountA(.Rows("2:" & .Rows.Count)) = 0 Then 'hide it .Visible = xlSheetHidden Else .Visible = xlSheetVisible End If becomes If Application.CountA(.Rows("2:" & .Rows.Count)) = 0 _ or .autofilter.range.columns(1).cells _ .specialcells(xlcelltypevisible).count = 1 Then 'hide it .Visible = xlSheetHidden Else .Visible = xlSheetVisible End If This does expect that you'e already added those arrows to each sheet. Scott Bass wrote: Thanks Dave, these macros below work great. I now have need for three (similar) macros... Say I have this worksheet: Var1 Var2 SomeData 1 0 A 0 1 B 1 1 C I need these macros: FilterData(): Turn on autofilter (if not already on) Filter where Var1=1 and Var2=0 (would return SomeData=A) ShowAllData(): Keep autofilter turned on, but show all rows HideSheets(): Call the FilterData() macro *** Hide the worksheet if the *filtered* data returns no rows (still have the header row) *** Or possibly just embed the FilterData criteria into the COUNTIF and/or DCOUNT function without turning on autofiltering for the end user. I've looked into the COUNTIF and DCOUNT functions, but can't quite work out the syntax. Any help appreciated. Thanks, Scott "Dave Peterson" wrote in message ... At least one sheet has to be visible in every workbook. To avoid any error that could occur by hiding sheets in the wrong order, I'd create a sheet that would always be visible. Then make sure that this sheet stays visible. In my code to hide sheets, I used the name "Instructions". But you could the name you want. Option Explicit Sub HideSheets() Dim wks As Worksheet Dim InstWks As Worksheet Set InstWks = Worksheets("Instructions") InstWks.Visible = xlSheetVisible For Each wks In ActiveWorkbook.Worksheets With wks If .Name = InstWks.Name Then 'skip it Else If Application.CountA(.Rows("2:" & .Rows.Count)) = 0 Then 'hide it .Visible = xlSheetHidden Else .Visible = xlSheetVisible End If End If End With Next wks End Sub Sub ShowAllSheets() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets wks.Visible = xlSheetVisible Next wks End Sub You can use this to assign the shortcut keys: Tools|Macro|Macros|Select a macro and click the Options button assign your shortcut key preference and click ok Then click cancel Scott Bass wrote: Hi, I have a workbook with worksheets created by an external ETL application. The end user needs to address data issues in some of the worksheets created. The workbook can get very wide, with many worksheets. I'd like to create macros to: * Hide_Empty_Worksheets An empty worksheet would only have a header row, i.e. row 1 contains Var1, Var2,...,VarN. No other data from row 2 and below. * Show_All_Worksheets Undo the action from above. I'd like to bind these macros to hotkeys, i.e. Cntl-Shift-H to hide and Cntl-Shift-S to show. Does anyone have similar macros that I could use, or point me in the right direction? Unfortunately I'm not an Excel macro guru, so if you're pointing me in the right direction, any helpful URL's for online resources to help with Excel programming would be useful. Thanks, Scott -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide empty worksheets
You can use something like:
dim wks as worksheet dim FoundCellVar1 as range dim FoundCellVar2 as range dim FoundCellSD as range For each wks in activeworkbook.worksheets with wks with .rows(1) set foundcellvar1 = .Cells.Find(What:="Var1", _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) set foundcellvar2 = .Cells.Find(What:="Var2", _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) set foundcellSD = .Cells.Find(What:="SomeData", _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) end with if foundcellvar1 is nothing _ or foundcellvar2 is nothing _ or foundcellsd is nothing then msgbox "headers are missing! exit sub '??????? else 'here's where you can use the .column from those 3 ranges msgbox foundcellvar1.column & vblf _ _ foundcellvar2.column & vblf _ _ foundcellsd.column end if ..... Scott Bass wrote: Oops, one more thing. My example was too simplistic. Most worksheets will have this structure (simplified example): Var1 Var2 SomeData but some worksheets will have this structu InputTable Var1 Var2 SomeData I need to autofilter, hide sheets, etc. based on the column name (Var1 & Var2), rather than column position. Is there some way in Excel to tell it that row1 defines the "name" of the column, and reference the column by name in the macro? Thanks, Scott "Dave Peterson" wrote in message ... Record a macro when you apply the filter to your worksheet and you'll have the code. Same thing when you show the data. As for hiding the sheet... If Application.CountA(.Rows("2:" & .Rows.Count)) = 0 Then 'hide it .Visible = xlSheetHidden Else .Visible = xlSheetVisible End If becomes If Application.CountA(.Rows("2:" & .Rows.Count)) = 0 _ or .autofilter.range.columns(1).cells _ .specialcells(xlcelltypevisible).count = 1 Then 'hide it .Visible = xlSheetHidden Else .Visible = xlSheetVisible End If This does expect that you'e already added those arrows to each sheet. Scott Bass wrote: Thanks Dave, these macros below work great. I now have need for three (similar) macros... Say I have this worksheet: Var1 Var2 SomeData 1 0 A 0 1 B 1 1 C I need these macros: FilterData(): Turn on autofilter (if not already on) Filter where Var1=1 and Var2=0 (would return SomeData=A) ShowAllData(): Keep autofilter turned on, but show all rows HideSheets(): Call the FilterData() macro *** Hide the worksheet if the *filtered* data returns no rows (still have the header row) *** Or possibly just embed the FilterData criteria into the COUNTIF and/or DCOUNT function without turning on autofiltering for the end user. I've looked into the COUNTIF and DCOUNT functions, but can't quite work out the syntax. Any help appreciated. Thanks, Scott "Dave Peterson" wrote in message ... At least one sheet has to be visible in every workbook. To avoid any error that could occur by hiding sheets in the wrong order, I'd create a sheet that would always be visible. Then make sure that this sheet stays visible. In my code to hide sheets, I used the name "Instructions". But you could the name you want. Option Explicit Sub HideSheets() Dim wks As Worksheet Dim InstWks As Worksheet Set InstWks = Worksheets("Instructions") InstWks.Visible = xlSheetVisible For Each wks In ActiveWorkbook.Worksheets With wks If .Name = InstWks.Name Then 'skip it Else If Application.CountA(.Rows("2:" & .Rows.Count)) = 0 Then 'hide it .Visible = xlSheetHidden Else .Visible = xlSheetVisible End If End If End With Next wks End Sub Sub ShowAllSheets() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets wks.Visible = xlSheetVisible Next wks End Sub You can use this to assign the shortcut keys: Tools|Macro|Macros|Select a macro and click the Options button assign your shortcut key preference and click ok Then click cancel Scott Bass wrote: Hi, I have a workbook with worksheets created by an external ETL application. The end user needs to address data issues in some of the worksheets created. The workbook can get very wide, with many worksheets. I'd like to create macros to: * Hide_Empty_Worksheets An empty worksheet would only have a header row, i.e. row 1 contains Var1, Var2,...,VarN. No other data from row 2 and below. * Show_All_Worksheets Undo the action from above. I'd like to bind these macros to hotkeys, i.e. Cntl-Shift-H to hide and Cntl-Shift-S to show. Does anyone have similar macros that I could use, or point me in the right direction? Unfortunately I'm not an Excel macro guru, so if you're pointing me in the right direction, any helpful URL's for online resources to help with Excel programming would be useful. Thanks, Scott -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help on Macro to hide empty rows | Excel Discussion (Misc queries) | |||
URGENT!! Use macro button to hide empty columns and rows...HELP!!!! | Excel Programming | |||
Macro to hide rows with empty cells | Excel Worksheet Functions | |||
Macro that will Pull Cells from Other Worksheets if not empty? | Excel Programming | |||
if cell empty hide row macro? | Excel Programming |