Macro that changes named range

Question

My workbook has a named range "Irange" in Name Manager. I ran a macro to edit the range so it includes all data from A1 to the last cell that has data in Sheet1 which is part of the active workbook. So I click Record Macro. Then I click Name Manager and Irange and highlight the contents of the Refers To box. Then I click Sheet1 and position the cursor in A1 and click Ctrl Shift End to get the whole sheet. When I edit the macro, it says:

ActiveWorkbook.Names("lrange").RefersToR1C1 = "=Sheet1!R1C1:R4895C4"

The R1C1 of course refers to cell A1 which is fine. But R4895C4 which is cell D4895 is an absolute value. The contents of Sheet1 keeps changing as more rows are added, so I don't want the macro to be limited to a specific cell as the ending value. Also, the commands are grayed out when I select the range in the Refers to box, so I can't click Use Relative Reference. How can I edit this to include the whole sheet?

Answer 1

Another option is to use the lesser used optional parameters within .Address(...) itself.

ActiveWorkbook.Names("lrange").RefersTo = Sheets("Sheet1").UsedRange.Address(1,1,xlA1,1)

The 1 in bold notes an external address that will include the workbook and worksheet name. Adding the workbook name may seem superfluous but it does not harm the definition of the range.

Answer 2

ActiveWorkbook.Names("lrange").RefersTo = "=Sheet1!" & Sheets("Sheet1").UsedRange.Address

works like a charm, exactly what I was looking for. Thanks much.

Answer 3

Hans,

Thanks for the quick reply. I can't use Option 2 as someone else creates the file and I have no control over how it is saved, so I'd rather use a macro. Option 1 works, but when I look at the Refers To box after running the macro, it says =Sheet1!$A$1:$D$30889. The named range is used in a vlookup so all those empty rows after the last row (row 4895) does not hurt. I selected all the cells below row 4895 and cleared it, just in case there was something in one of those cells. But it still ranges to row 30889. Just curious as to why? Also, what if more columns are added?

Answer 4

Looked at another way, you can define a named range within the Formulas, Name Manger with a formula that dynamically adjusts the size of the range. Something like,

=OFFSET($A$1, 0, 0, COUNTA($A:$A), COUNTA($1:$1))

I do not know the nature of your data and that formula might require some tweaking but this method will ensure that your named range adjusts to include new rows and/or columns being added.

Answer 5

Option 1:

Calculate the last used row and use that in the macro:

Dim LastRow As Long

LastRow = Range("A:D").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

ActiveWorkbook.Names("lrange").RefersTo = "=Sheet1!$A$1:$D$" & LastRow

Option 2: this can be used if there is a column without blanks in between the filled cells.

Create a dynamic named range - no code needed.

Let's say that column A won't contain blanks.

In Name Manager, make the range refer to

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),4)

This dynamic range will automatically expand if new rows are filled, and contract if rows at the bottom are cleared.