Macros - variable columns

[Evan]

Hello,
Not sure if this question has been answered but I could not find it. I have
a macro that hides a few columns in a spreadsheet. However, everytime I
insert or delete a column in the spreadsheet, the macro only retains the
column number and will no longer hide the columns I want to hide. Is there
an edit I can make to the macro to make it dependent on me inserting or
deleting columns?

Thanks for the help!
-Evan

 

[Dave Peterson]

If you always want to hide certain columns, you could give a cell in that column
a nice name (insert|name in xl2003 menus). Then use that to hide that column.

worksheets("sheetnamehere").range("myStateColumn").EntireColumn.hidden = true

Or if you can't name the sheet, maybe you know what's in the header (for
example).

Dim FoundCell as range
With worksheets("SheetNameHere").rows(1)
Set FoundCell = .Cells.Find(What:="State", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
end with

if foundcell is nothing then
'can't find it to hide it!
else
foundcell.entirecolumn.hidden = true
end if