What is the last cell in a worksheet ?

[sean_f_howard]

Why is it that

ActiveSheet.SpecialCells(xlCellTypeLastCell))

sometimes gives a different range from

ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell))

The issue arose by setting the print range for a worksheet. The first solution worked OK, but usually included an extra (and indeed empty) column from the far right of the worksheet.
The solution works great for all cases but I want to know if anybody can tell me why this is so.

 

[Andrew Poulsom]

For me your first code statement produced an error. It should be:

ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)

The UsedRange expands as you enter more data on a worksheet. But it does not contract as data is removed - you need to save the workbook to update it. So the first statement can return an unexpected result.

Accessing the UsedRange property actually updates the range (like saving the workbook), so SpecialCells returns the expected result.

 

[sean_f_howard]

Thanks for that Andrew. It kind of makes sense and I shall simply remember to use the two combined whenever possible.

On the same theme sometimes I have a worksheet whose last cell is way beyond the actual cells used in the worksheet. I have tried clearing,deleting column/rows, saving etc. but all to no avail. The ONLY thing that seems to reset the last cell to it's correct address is copying the worksheet, then the copy is right (but the original still wrong)
Am I missing a simple solution to this. I do not want to always copy entire worksheets as it seems a bit silly

 

[Tom Urtis]

SpecialCells LastCell, UsedRange and CurrentRegion are iffy and give bad results depending on the version, when the workbook was saved, formats, and if breaks occur in the data. This is the most reliable way I know, as long as you are starting from cell A1 (modify if not).

Sub Test1()
Dim LR As Long, LC As Integer, MyRange As Range
LR = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
LC = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Set MyRange = Range(Cells(1, 1), Cells(LR, LC))
MyRange.Select 'or Copy or whatever
End Sub

 

[Andrew Poulsom]

I seem to recall that in older versions of Excel (I have Excel 2000) referencing a cell beyond the UsedRange in a formula expanded the UsedRange. But I haven't experienced that recently.

Maybe just accessing the UsedRange in code would solve your problem:

MsgBox ActiveSheet.UsedRange.Address

Also look here:

http://support.microsoft.com/?kbid=244435

 

[sean_f_howard]

To Andrew:
[

MsgBox ActiveSheet.UsedRange.Address

did not make any difference to the last cell

To Tom:

Sub Test1() 
Dim LR As Long, LC As Integer, MyRange As Range 
LR = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row 
LC = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column 
Set MyRange = Range(Cells(1, 1), Cells(LR, LC)) 
MyRange.Select 'or Copy or whatever 
End Sub

did work and I will probably use it although it seems a bit long winded for something which "should" be simple


I would like to thank you both for your ideas as it proves to me once again that there are very few original problems in Excel and someone always seems to have some idea (you just have to find the right forum)

Thanks again

 

[Andrew Poulsom]

I agree with Tom about flakiness, but try this experiment.

Open a blank workbook and run this code:

Sub Test()
   Dim Msg As String
   Msg = "Cells before " & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Address(False, False) & vbCrLf
   Msg = Msg & "UsedRange " & ActiveSheet.UsedRange.Address(False, False) & vbCrLf
   Msg = Msg & "Cells after " & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Address(False, False)
   MsgBox Msg
End Sub

You should get A1 in all instances. Enter something in B5 and run it again. You should get B5 in all instances. Delete the contents of B5 and run it again. You should get B5, A1, A1. So accessing the UsedRange property is resetting the UsedRange.