VBA macro to hide certain columns even when new columns have been added

  • Thread starter Thread starter vow.jackofhearts
  • Start date Start date
V

vow.jackofhearts

I have created a spreadsheet that contains several buttons for
different views. Each of these buttons runs its own vba macro that
hides certain columns in the spreadsheet. I am trying to figure out
how I can set this up so that if a user adds a new column to the
spreadsheet, it still hides the same columns. For example, I have
columns A,B,C,D, and E. I have a macro that hides column C. If a
user adds a new column to the beginning of the spreadsheet, I want
that button to still hide the old column C (now column D). Any
thoughts on how I could do this?
 
One way is to give for example the first cell of the column a name(Ctrl-F3)

You can use this then
Range("MyColumn1").EntireColumn.Hidden = True
 
I have encountered an issue and I'm not quite sure how to get around
it. If the user deletes one of the columns for their customized use,
and then try the macro it produces an error because "MyColumn1" is no
longer a valid cell name. I would like the macro to procede normally
and simply ignore that column. Any suggestions?
 
One way is to ignore the error:

on error resume next
Range("MyColumn1").EntireColumn.Hidden = True
on error goto 0
 
On error resume next
means that the next line may cause an error (or not) -- and I know/accept that.

on error goto 0
says Please go back to checking for errors. (I know I don't have any more that
you can find!)
 
From Help

On Error Resume Next Specifies that when a run-time error occurs, control goes
to the statement immediately following the statement where the error occurred
where execution continues. Use this form rather than On Error GoTo when
accessing objects.

On Error GoTo 0 Disables any enabled error handler in the current procedure.


Gord Dibben MS Excel MVP
 
Back
Top