Usedrange would give the same result for the farthest used cell as
xlLastCell. Gary makes the common mistake of assuming that A1 is in the
usedrange. While it may be, it isn't necessarily. Which to use (if you
have lost interest in the original issue), depends on what you want.

Also, like I said, the original problem is not circumvented with Usedrange.
You might want to do a bit more testing associated with your original
problem.

--
Regards,
Tom Ogilvy


" wrote:

Thanks to Both of you. Both methods work.

Gary''s Student wrote:
Your code does not select the last cell, it is selecting the whole range
between A1 and the last cell. If this is what you want to do then:

Sub SetData()
Worksheets("Data").Activate
ActiveSheet.UsedRange.Select
Selection.Name = "Data"
End Sub

will work just as well.
--
Gary's Student


" wrote:

I'm using the following code to select a all the data on a sheet and
assign it a named range

Sub SetData()

Worksheets("Data").Activate

'Select last cell in data range, give is a local name "Data"
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Selection.Name = "Data"

End Sub

I noticed that if I reduced the size of the data range and run the code
the updated range selection is not the new smaller range, but the old,
larger range. Its as if SpecialCells(xlLastCell) looks for the last
cell ever used, not necessarily the last cell in the range.

Is there a more appropriate method to use than SpecialCellc?

or

Is there a to scrub the unused cells so that SpecialCells(xlLastCell)
doesn't recognize them?

Thanks
Robert