SpecialCells(xlLastCell) selects old data

[robert.hatcher]

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

 

[Guest]

You are misunderstanding the meaning of xlLastCell. Excel stored detailed
information on cells considered in use (not necessarily containing data).
This is a rectangular area and the lower right corner of that area is what is
revealed by doing xlLastCell or the entire area by doing
Activesheet.UsedRange


If you want the last cell with data, you will need to search for it with
code like

Sub GetRealLastCell()
Dim RealLastRow As Long
Dim RealLastColumn As Long
Range("A1").Select
On Error Resume Next
RealLastRow = _
Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
Cells(RealLastRow, RealLastColumn).Select
End Sub

for resetting the range, see Debra Dalgleish's

Used Range Reset
http://www.contextures.com/xlfaqApp.html#Unused

 

[Guest]

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.

 

[robert.hatcher]

Thanks to Both of you. Both methods work.

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.

 

[Guest]

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.