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)?
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
try this
Set workRange = sht.Range("B1", Cells(numRows, numColumns))
ActiveWorkbook.Names.Add Name:="myName", RefersToR1C1:=workRange.Address
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)
