Range Names
Posted by Paul Johnson on January 08, 2002 12:26 PM
I asked a similiar question yesterday - and got a great answer. Now I have a variation of the question.
what is the best way to define and name a range w/ VBA
I am able to select my range like this:
Sheets("data").Range("a1").Select
Range(ActiveCell, ActiveCell.End(xltoright)).Select
Range(ActiveCell, ActiveCell.End(xldown)).Select
But - What is the BEST way to select and name a range for all data on a sheet ?
The rows & Columns could be different each time the user runs the macro.
Your help is appreciated ...
PJ
Posted by Dan on January 08, 2002 12:42 PM
Sheets("Tues").Range("A1").Select
Range(Selection, Selection.SpecialCells(xlCellTypeLastCell)).Select
Posted by Paul Johnson on January 08, 2002 12:45 PM
Where does this name the range ?
Posted by Dan on January 08, 2002 12:51 PM
Next time I'll read the post :)
Sheets("data").Range("A1").Select
ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:=Range(Selection, Selection.SpecialCells(xlCellTypeLastCell))
Posted by paul johnson on January 08, 2002 1:54 PM
one more question
Thank you for responding.
the code works, however, if the data range changes and the macro re-run(smaller) the range does not change. If it is larger it expands.
I would like to have it adjust either way.
Any sugegstions ?
PJ
Posted by Dan on January 09, 2002 6:25 AM
Re: one more question
Yes, the "LastCell" function is a bit quirky to say the least, but I wasn't sure if it would be a problem from looking at your original question. If you really want this done right, here's an adaptation of code that I found that works very well.
Sub setRange()
Dim LastRow, LastCol, LastCell
'Removes runtime error if worksheet is blank
On Error Resume Next
With Worksheets("Tues")
LastRow = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
LastCol = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
End With
Set LastCell = Cells(LastRow, LastCol)
ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:=Range("A1", LastCell)
End Sub
