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
