MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to hide empty columns within a range


Posted by Dwight on June 06, 2001 4:56 AM

columns B-M represent 12 months with rows 6-60 empty until dollar amounts are entered, rows 1-5 have various text and data, and row 61 is a formula which sums the amounts entered in 6-60. Would like a macro which will hide any columns in the range B6:M60 in which data has not yet been entered in that column.

Any ideas?
Thanks,
Dwight


Posted by Gerd on June 06, 2001 7:14 AM


To hide columns with no data in rows 6:60 :-
Sub HideColumns()
Dim col As Range
For Each col In Columns("B:M")
If Application.Count(Range(Cells(6, col.Column), Cells(60, col.Column))) = 0 Then
col.Hidden = True
End If
Next
End Sub

To unhide columns B:M so that data can be entered :-
Sub UnhideColumns()
Columns("B:M").Hidden = False
End Sub


Posted by Gerd on June 06, 2001 7:15 AM


To hide columns with no data in rows 6:60 :-
Sub HideColumns()
Dim col As Range
For Each col In Columns("B:M")
If Application.Count(Range(Cells(6, col.Column), Cells(60, col.Column))) = 0 Then
col.Hidden = True
End If
Next
End Sub

To unhide columns B:M so that data can be entered :-
Sub UnhideColumns()
Columns("B:M").Hidden = False
End Sub