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