CODEWITHSUNDEEP
excelvbarangecells
Jotter
Jotter

Reputation: 91

Set range from used range offset

I would like to build a range to do work on. I am able to return the row and column indices of last used cell but how do I include this in a named range?

numRows = sht.Cells(Rows.Count, 2).End(xlUp).Row
numColumns = sht.Cells(1, Columns.Count).End(xlToLeft).Column

Set endRange = sht.Cells(numRows, numColumns)

Set workRange = sht.Range("B1", Cells(numRows, numColumns)?

Upvotes: 1

Views: 1524

Answers (3)

Amorpheuses
Amorpheuses

Reputation: 1423

This should work:

With Sheets("Sheet1")
        Set rng = .Range("B1:" & .Cells(numRows, numColumns).Address)
        ThisWorkbook.Names.Add Name:="workRange", RefersTo:=rng
End With

Note that you can run this any number of times and it will simply overwrite the existing named variable "workRange" without any problems.

To build it up as a series of non-contiguous ranges you simply use the above but append the additional ranges on to the string with commas like this:

Set rng=.Range("B1:C2,E4:F6,G2:" & Cells(numRows,numColumns).Address)

Upvotes: 1

user3598756
user3598756

Reputation: 29421

With sht
    numRows = .Cells(.Rows.Count, 2).End(xlUp).Row
    numColumns = .Cells(1, .Columns.Count).End(xlToLeft).Column

    Set endRange = .Cells(numRows, numColumns)

    Set workRange = .Range("B1", endRange)

    .Parent.Names.Add Name:="myName", RefersToR1C1:=workRange.Address
End With

Upvotes: 1

nightcrawler23
nightcrawler23

Reputation: 2066

try this

Set workRange = sht.Range("B1", Cells(numRows, numColumns))
ActiveWorkbook.Names.Add Name:="myName", RefersToR1C1:=workRange.Address

Upvotes: 1

Related Questions