How To Resize A Named Range In Excel VBA
To resize a named range in Excel VBA it’s best to get an object reference to the range and then use the Excel VBA resize method to resize the range.
With the resized range object you then set it’s name property to the range name that you want resized.
Excel will behind the scenes delete the old range name and replace it with the one you have just created.
The spreadsheet above (left) has a named range called "nmeCustomerData"
As you can see "nmeCustomerData" needs to be readjusted to refer to the complete data range.
This can be done via the user interface, however the focus here is on how to do this via vba.
The VBA Code Below Will Resize The Range Variable
Line 4 gets a reference to the Excel named range and then sets it to the current region for that range.
This is the same as manually clicking in the range and pressing "F5" then clicking "Special" then "Current Region".
01 Sub ResizeNamedRange() 02 Dim rngData As Range ' Create rngData object variable 03 04 Set rngData = ThisWorkBook.Names("nmeCustomerData").RefersToRange.CurrentRegion 05 Set rngData = rngData.Resize(rngData.Rows.Count -1).Offset(1) 06 rngData.Name = "nmeCustomerData" 07 Set rngData = Nothing 08 End Sub
Line 5 then resizes the range which now refers to all of the data including the header (which we don’t want) to one row less than all of the data and then offsets the range by one row.
The purpose of this is to account for the header and then move the selection down so the data excluding the header is referenced.
Line 6 then applies the existing range name to the name property of this resized range variable.
And that’s it you have now resized an existing range name in Excel VBA.
You can get the macro spreadsheet that goes with this article in the yellow box above, this will also notify you when I publish other articles that may be of interest to you. You can easily opt out of these article notifications at any time.