CODEWITHSUNDEEP
vbaexcel
beeba
beeba

Reputation: 432

Variable Named Ranges in Excel

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?

Upvotes: 1

Views: 789

Answers (2)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19847

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

Upvotes: 3

PaichengWu
PaichengWu

Reputation: 2689

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

Upvotes: 1

Related Questions