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.
Get the spreadsheet used in the tutorial video above.
Also get updates when new blog or video tutorials are released.
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.
Great work and very helpful tutorial, it makes it easier to understand other more experienced user code
Perfect, I’d forgotten how to do this!
Glad I could help
Doesn’t CurrentRegion grab all adjacent cells with data in them? Is this therefore assuming no adjacent data in the cells adjacent to the named region, or am I missing something?
You’re correct, the idea is to ensure in your spreadsheet design that you don’t have adjacent cells to your main data ranges.
And teach other users of your spreadsheet to have the same approach, i.e. data ranges consist of a header and data and adjacent cells must not be populated.
Said another way, there must be a blank row and column surrounding the data range if it’s not at the top and or left of the spreadsheet.
Hi Sean, this is very helpful.. How can I adjust this code if my range is a single column and I just want to adjust the number of rows in the range? Thanks
Yes, it works for single columns also.