HIdden Cells?

R

Rosemary

I would like to be able to hide columns automatically in a
spreadsheet if no data was entered in the cells for that
column without having to highlight the column and choosing
hide. Eg: The spreadsheet has column headings, but for
printing purposes I would like to hide that column if no
data has been entered under any number of headings. Is
there anyway this can be done automatically?
Thanks for your help
 
D

Dave Peterson

I wouldn't do it automatically when printing, but I would use a macro that I
could run on demand:

Option Explicit
Sub hideCols()

Dim iCol As Long

With ActiveSheet
For iCol = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
If Application.CountA( _
.Cells(2, iCol).Resize(.Rows.Count - 1)) = 0 Then
.Columns(iCol).Hidden = True
Else
.Columns(iCol).Hidden = False
End If
Next iCol
End With

End Sub
Sub showAllCols()
ActiveSheet.Columns.Hidden = False
End Sub
 
E

Eilidh

I wanted to do exactly the same thing, but with the unused rows
instead of the unused columns. Do I just switch Col with Row, or will
that not work?
 
D

Dave Peterson

It depends on how much you switched?

Did you remember to reverse the insides of the .cells(row,col) stuff? And
xltoleft needs to be modified to xlup and a little bit more.

Option Explicit
Sub hideRows()

Dim iRow As Long

With ActiveSheet
For iRow = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
If Application.CountA( _
.Cells(iRow, 2).Resize(, .Columns.Count - 1)) = 0 Then
.Rows(iRow).Hidden = True
Else
.Rows(iRow).Hidden = False
End If
Next iRow
End With

End Sub
Sub showAllRows()
ActiveSheet.Rows.Hidden = False
End Sub

This still looks at column A for headers and hides rows that have nothing in
them in B:IV. Is that really what you wanted?
 
Top