Hide Columns Based on Condition

[Skornia115]

New to VBA and need help.

I am trying to hide columns based on a condition. My workbook has
numerous worksheets. Each worksheet contains data for a specific
"account" and each column within the "account" worksheet has month end
data. Therefore row 1 of each worksheet contains month-end values and
each row below has various data for that specific "account."
Furthermore, the first worksheet, which basically serves as a "menu",
has a cell with a specific date. What I would like to do is write a
macro in each worksheet representing an "account" that hides all
columns in the "account" worksheets that have not occurred yet. (i.e.
If the date on the menu worksheet is 3/31/05, I would like all columns

that have month-end values exceeding 3/31/05 to be hidden). Any
suggestions?

 

[Guest]

Change sMenu to hold the name of the Menu sheet and rngDate to refer to the
cell containing the date.

Sub Hidecolumns()
Dim sh as worksheet, rng as Range, cell as Range
Dim sMenu as String, rngDate as Range
sMenu = lcase("menu")
set rngDate = worksheets(sMenu").Range("B9")
for each sh in Worksheets
if lcase(sh.Name) <> sMenu then
sh.Columns.Hidden = False
set rng = sh.Range(sh.Cells(1,1),sh.Cells(1,"IV").End(xltoLeft))
for each cell in rng
if isdate(cell).Value then
if cell.Value > rngDate.Value then
cell.EntireColumn.Hidden = True
end if
end if
next
end if
Next
End Sub

 

[Skornia115]

Thanks Tom. If I want this to occur automatically, is there anything
specific I need to do with the syntax?

 

[Skornia115]

Thanks Tom. If I want this to occur automatically, is there anything
specific I need to do with the syntax?

 

[Guest]

I see there were a couple of typos in the original, but I would see it firing
only when you change the date in the menu sheet - my interpretation of
automatically.

right click on the sheet tab of that sheet and select view code, then put in
code like this in the resulting module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet, rng As Range, cell As Range
Dim sMenu As String, rngDate As Range
sMenu = Me.Name
Set rngDate = Worksheets(sMenu).Range("B9")
If Target.Address = rngDate.Address Then
For Each sh In Worksheets
If LCase(sh.Name) <> sMenu Then
sh.Columns.Hidden = False
Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, "IV").End(xlToLeft))
For Each cell In rng
If IsDate(cell.Value) Then
If cell.Value > rngDate.Value Then
cell.EntireColumn.Hidden = True
End If
End If
Next
End If
Next
End If

End Sub

If you want something different, you would have to define automatically.

 

[Skornia115]

I have tried pasting this in with no luck. I just want to make sure I
am doing everything correctly. The name of the menu worksheet is Menu
and the cell within the Menu worksheet I want referenced is B9. This
is what I have:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet, rng As Range, cell As Range
Dim Menu As String, rngDate As Range
Menu = Me.Name
Set rngDate = Worksheets(Menu).Range("B9")
If Target.Address = rngDate.Address Then
For Each sh In Worksheets
If LCase(sh.Name) <> Menu Then
sh.Columns.Hidden = False
Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1,
"IV").End(xlToLeft))
For Each cell In rng
If IsDate(cell.Value) Then
If cell.Value > rngDate.Value Then
cell.EntireColumn.Hidden = True
End If
End If
Next
End If
Next
End If


End Sub


Thanks for your patience.

 

[Guest]

I assumed you would not have dates going all the way to column IV. It worked
for me in that case. If you do have dates going all the way to column IV
change the code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet, rng As Range, cell As Range
Dim sMenu As String, rngDate As Range
sMenu = LCase(Me.Name)
Set rngDate = Worksheets(sMenu).Range("B9")
If Target.Address = rngDate.Address Then
For Each sh In Worksheets
If LCase(sh.Name) <> sMenu Then
sh.Columns.Hidden = False
' Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, "IV").End(xlToLeft))
Set rng = sh.Range("A1:IV1")
For Each cell In rng
If IsDate(cell.Value) Then
If cell.Value > rngDate.Value Then
cell.EntireColumn.Hidden = True
End If
End If
Next
End If
Next
End If

End Sub

 

[Guest]

Also, you have to edit the cell/change the date in cell B9 on sheet menu to
make it fire.

 

[Skornia115]

So all I need to do is paste the syntax exactly as it is into each
worksheet, or do I need to change how the Menu worksheet is referenced
in the syntax. Also, which worksheets do I paste this in....the "menu"
or each "account".

 

[Guest]

Based on my understanding of what you want to do and my assumption that it
would only need to run if you change the date in B9 on the sheet menu,
Just place it in the menu sheet code module.