How to Insert Rows and Avoid Re-Doing a macro Union Range

[Snake Eyes]

Greeting All,
I have 10 sections in an Excel Sheet that have 3 macros for each section to manipulate the values in Union Ranges within those 10 sections. I need to Insert Rows at the top of the sheet to add more information however, when I do this it requires that re-do each of the 30 macros and re-select the Union Ranges so that the macros manipulate the correct cells.
How can I avoid this tedious task?

This is an example of one of the Union Ranges.

     Union(Range( _
        "G671:H672,G676:H677,G681:H682,G686:H687,J686:K687,J681:K682,J676:K677,J671:K672,J666:K667,J661:K662,J656:K657,J651:K652,J646:K647,J641:K642,M641,M646,M651,M656,M661,M666,M671,M676,M681,M686,Q692:R695,C633:E636,G633:G636,H634:H636,I633,K633:K636,M633:M636" _
        ), Range( _
        "D646:D647,D651:D652,D656:D657,D661:D662,D666:D667,D671:D672,D676:D677,D681:D682,D686:D687,E686,E681,E676,E671,E666,E661,E656,E651,E646,E641,G641:H642,G646:H647,G651:H652,G656:H657,G661:H662,G666:H667" _
        )).Select

 

[mikerickson]

If you've hard coded ranges and then insert a row, the only way to alter the code is one hard code at a time. (i.e. all 30 macros).
For future changes, you might want to base your routines on named ranged , i.e. create a NamedRange that refers to Sheet1!$A$1 and use Range(myNamedRange) rather than Sheets("Sheet1").Range("A1")

You also might look at putting your data into a more useable lay-out, two rows in, three rows out in column G, a different 2in,3out in H, etc is not the best data layout.

 

[Tupe77]

There isn’t much to do or actually there’s too much craft to do.

What if you ran all your Union macros and made each of them a NamedRange region. After that, adding a row wouldn't hurt. Anyway, you'll have to change your code, but maybe it would be easier with NamedRange when you don't have to go through all the cell references?