Macro to hide empty worksheets

[Scott Bass]

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]

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]

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]

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...

becomes

or .autofilter.range.columns(1).cells _
.specialcells(xlcelltypevisible).count = 1 Then
This does expect that you'e already added those arrows to each sheet.

 

[Dave Peterson]

Another way if you're using that data|filter|autofilter to hide the rows--and
you have something in column A:

=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]

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 structure:

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]

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
.....

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 structure:

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