Make ranges in a sort macro change when I insert rows

I use the below macro to sort a report within my workbook, if I add rows of data at any point I have to manually change all the cell references/ranges

Is it possible to write the code in a way that automatically changes the cell references/ranges if I insert a row at any point

Please Login or Register  to view this content.

Use named ranges for each area of the sheet. You might need to be careful about catching rows added at the end of an area so you might need to use OFFSET against the row after the area.

Don't use .Select ... Selection. ... Use With ... End With. It will be quicker and won't move the selection point around the sheet.

Regards, TMS

Hi TMS

Thx for the reply, my visual basic is not good, I did however look at named ranges and was ok if the row inserted was in the first block ie between row 7 and 11, I could get this to change to be 7:12, but how do you then relate this to make sure the second block ie rows 18:21 update to 19:22 etc

Thx in advance for any help

Please post a sample workbook. I'd like to see the layout.

If you create a named range for each area, they will all auto adjust if you add rows.

Sample file attached

Ok, a couple of ways of defining the named ranges.

0. Fixed: ='Retail Report'!$A$7:$H$11

1. Dynamic: Block_AO: ='Retail Report'!$A$7:OFFSET('Retail Report'!$H$12,-1,0)

2. Dynamic: Block_AI: ='Retail Report'!$A$7:INDEX('Retail Report'!$H:$H,MATCH("A Total",'Retail Report'!$A:$A,0)-1)

although the first is shorter, it is volatile so I prefer to avoid it.

and then the code would be modified to:

Please Login or Register  to view this content.

You need to define a named range for each of the areas that you want to sort. Then use that as in the code example. Just copy the code changing Block_AI to Block_BI, Block_CI, ... etc.

Bit of a pain to set up ... though you can copy and edit the ranges. But, when it's done, the code should be fairly static. If you copy the range from one named range, when you paste it into the new named range, press F2 before you start editing ... makes it easier.

Regards, TMS