Show / Hide macro issue

[Fishboy]

Hi all,

I have successfully added in a number of show / hide macros attached to buttons in order to manage the visibility of various rows / columns in an Excel 2010 spreadsheet.

Over time, as more data has been provided it has become necessary to insert additional columns or rows into the spreadsheet, but doing so obviously throws all of the show / hide macros out of whack.

Am I missing a trick here? Is it possible to use relative column or row labels which will dynamically update when new columns or rows are added, or do I have to manually go back through all of the macros and update them with the new values?

Here is an example of what I have been using so far:

Sub Branch 2()
Columns("C:C").Hidden = Not Columns("C:C").Hidden
Columns("E:AB").Hidden = Not Columns("E:AB").Hidden
End Sub

The above will hide / show all columns from C to AB, leaving only D showing. I have a similar macro tailored for each column for all the other office locations, which are listed alphabetically across Row 2, starting in column C.

Now I need to add in new office locations, which will also need to fit in alphabetically to the current list of branches. This means inserting new columns at the relevant alphabetic location, but doing so means the macros for all columns to the right of the new one no longer tie in with the correct column references in the existing macros.

This is driving me mad and I am one step away from just manually updating all the macros, but I know this is likely to come up again in the same spreadsheet in the future and I will have to go through it all over again.

Is there a way to keep the macro relative to its location, which updates dynamically if the column moves?

 

[InaCell]

Hi Fishboy,

I will stand corrected but I believe the macro code will have to be updated manually.

Cheers, InaCell.

 

[Fishboy]

Thanks InaCell, I feared as much. I'm going to hold out a little longer and see if anyone else has any suggestions, but it does look like it's going to be a heavily manual process

 

[InaCell]

You can still use replace in Visual Basic and select Current Project to use all Modules. Might make it a bit faster.

Cheers, InaCell.