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

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?
 
R

Ron de Bruin

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
 
V

vow.jackofhearts

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?
 
D

Dave Peterson

One way is to ignore the error:

on error resume next
Range("MyColumn1").EntireColumn.Hidden = True
on error goto 0
 
D

Dave Peterson

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!)
 
G

Gord Dibben

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top