Last cell

E

Elsie

Hi, how do I select the last cell of all my data in a worksheet? something
equivalent to when you press ctrl+end..

I want to select the last cell to the home cell and set these as the print
area....


Elsie
 
P

Paulw2k

Hi,

The last cell may not be the one you expect as XL remembers every used cell
so that
the UsedRange may be far larger than you want to print.

The function below returns the 'actual' last cell, given the existing data
spread.

So,

Sub YourProcedure()
.....
Dim rngLastCell as Range
dim sPrintAdrs as String
......

set rngLastCell = RealLastCell(ActiveSheet)

sPrintAdrs = Range("A1:" & rngLastCell.Address)
ActiveSheet.PageSetup.PrintArea = sPrintAdrs

......

End Sub


Function RealLastCell(ws As Worksheet) As Range
Dim LastRow As Long, LastCol As Integer

' Error-handling is here in case there is not any
' data in the worksheet

On Error Resume Next

With ws
' Find the last real row
LastRow = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
' Find the last real column
LastCol = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
End With

' Finally, initialize a Range object variable for
' the last populated row.
If LastCol = 0 And LastRow = 0 Then
Set RealLastCell = ws.Cells(1, 1)
Else
Set RealLastCell = ws.Cells(LastRow, LastCol)
End If
End Function



Regards

Paul
 
L

Leo Heuser

Hi Dave

I have not tested it thouroughly, but it looks like the line

Set DummyRange = Sheets(SomeName).UsedRange

resets UsedRange for the corresponding sheet.

Try this code:

Sub TestForLastCell()
'Leo Heuser, 25 Apr. 2004
'In an empty sheet enter data in J12 and M20
'and run TestForLastCell from that sheet
Dim Sh As Worksheet

Set Sh = ActiveSheet

'LastCell is M20
MsgBox "Last cell is " & Sh.Cells.SpecialCells(xlLastCell).Address

Sh.Rows("13:20").Delete

'LastCell should now be J12, but...
MsgBox "Last cell is still " & Sh.Cells.SpecialCells(xlLastCell).Address

Call ResetUsedRange(Sh)

'UsedRange is reset and LastCell is correct (J12)
MsgBox "Last cell is now " & Sh.Cells.SpecialCells(xlLastCell).Address

End Sub

Sub ResetUsedRange(Sh As Worksheet)
Dim DummyRange As Range
Set DummyRange = Sh.UsedRange
End Sub
 
D

Dave Peterson

Actually, Deb's code:

For Each wks In ActiveWorkbook.Worksheets
With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange

is in the middle of a "for each" loop, so it'll get all the worksheets in that
activeworkbook.
 
L

Leo Heuser

I know that, Dave.
My point was, that it might be possible to use

Set DummyRange = Sheets(SomeName).UsedRange

in connection with the *SpecialCells method* to reset
UsedRange for a sheet.

LeoH
 
D

Dave Peterson

Ahhh.

I thought you were saying that there was something wrong with Deb's code
(gasp!).
 
D

David McRitchie

Yes, see my page
http://www.mvps.org/dmcritchie/excel/lastcell.htm#resetall
It is based on a John Walkenbach's Tip 53
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Leo Heuser said:
I know that, Dave.
My point was, that it might be possible to use

Set DummyRange = Sheets(SomeName).UsedRange

in connection with the *SpecialCells method* to reset
UsedRange for a sheet.

LeoH
Click to expand...
 
D

Dave Peterson

As the owner said to me when I was walking through the China shop: You break
it, you bought it.

And that's no bull!
 
Top