Variable Named Ranges in Excel

Question

I have a table in Excel formatted as follows:

Date      Asset Return
1/3/2005    0.003582399
1/4/2005    -0.01908258
1/5/2005    0.002080625
1/6/2005    0.005699497
1/7/2005    -0.008040505
1/10/2005   -0.00339116
1/11/2005   -0.009715187
1/12/2005   0.002371855
1/13/2005   -0.00580783
1/14/2005   0.001058481
1/18/2005   0.015483842
1/19/2005   -0.014690715
1/20/2005   -0.015714799
1/21/2005   -0.010796326

I need a named range to reference each column. The workbook is a template, so the named range won't always cover the same number of rows depending on the data. I want to set it so that the named range "Date" and the named range "Asset Return" are automatically sized to cover the entire column from the first value until the last, without going past the last value in the column.

It will always start at cell B8, but might end at a different row depending on the size of the data.

How can I set a dynamic named range to accomplish this?

Darren Bartrup-Cook · Accepted Answer · 2016-08-03 15:26:51Z

3

This named range formula will do it:

=Sheet1!$B$8:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$B:$B)+8)

Remember to add the sheet name as the named range will operate on the active sheet otherwise.

The formula starts takes B8 as it's starting point: Sheet1!$B$8
It then counts how many cells are not blank in column B: COUNTA(Sheet1!$B:$B)
It adds 8 to the count (assuming your first rows are blank).
It then uses INDEX and the COUNTA to reference the last cell.

https://support.office.com/en-gb/article/INDEX-function-a5dcf0dd-996d-40a4-a822-b56b061328bd
https://support.office.com/en-gb/article/COUNTA-function-7dc98875-d5c1-46f1-9a82-53f3219e2509

edited Aug 3, 2016 at 15:26

answered Aug 3, 2016 at 15:20

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

PaichengWu Over a year ago

I would prefer =OFFSET(Sheet1!$B$8,0,0,COUNTA(Sheet1!$B:$B),1)

Taosique Over a year ago

Wow, never knew items in colon range notation could be calculated!! Great!

Darren Bartrup-Cook Over a year ago

You can use OFFSET, but that's a volatile function unlike INDEX. Meaning it will recalculate every time Excel recalculates regardless whether precedent data is changed (pretty much copied that word for word, so here's the link: decisionmodels.com/calcsecretsi.htm).

beeba Over a year ago

I was returning to this old code and just had a quick question for you. How would I adjust this formula to apply to a range rather than a column, for instance A:K starting from A1? I tried to change the formulas accordingly but without success.

Darren Bartrup-Cook Over a year ago

If the only data in the column is the data you're after you can use =Sheet1!$A$1:INDEX(Sheet1!$K:$K,COUNTA(Sheet1!$A:$A)). The INDEX is returning column K. COUNTA should still be referencing a column that doesn't have any blank cells in your data set - this will tell it which row to return in column K.

PaichengWu · Accepted Answer · 2016-08-03 15:24:19Z

1

Try this VBA code

Sub test()
    application.DisplayAlerts = false
    Range("B8").currentregion.createnames _
        top:true, right:=false, left:=false, bottom:=false
    application.DisplayAlerts = true
end sub

edited Aug 3, 2016 at 15:24

answered Aug 3, 2016 at 15:17

PaichengWu

2,6891 gold badge16 silver badges28 bronze badges

Collectives™ on Stack Overflow

Variable Named Ranges in Excel

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related