Hide Columns Based on Condition
 

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?

Hide Columns Based on Condition
 

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(xltoLe ft))
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

--
Regards,
Tom Ogilvy

" wrote:

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?