HIdden Cells?

  • Thread starter Thread starter Rosemary
  • Start date Start date
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
 
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
 
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?
 
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?
 
Back
Top