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.

Copy cells from one range to another using vba

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 and code that goes with this article
and associated YouTube video

Sign up below
and also get notified when new BusinessProgrammer
blog or video tutorials are created.

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.

Free Email Updates Get the latest content first.

If you have some coding experience, then this or both books may be best.


Disclosure:- If you buy this or any book using the link above (at the moment amazon.com only). Amazon will give me a small commission.

Thanks for reading this post

If you have any queries or comments please leave them below as they help to improve the quality of the information that I provide.

If you found this article useful then don't forget to share it by clicking on one of the social media buttons below.

Leave a Reply

Your email address will not be published. Required fields are marked *