Last cell

  • Thread starter Thread starter Elsie
  • Start date Start date
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
 
ActiveSheet.PageSetup.PrintArea = ActiveSheet.UsedRange.Address
 
Hi Elsie

One way:

ActiveSheet.Cells.SpecialCells(xlLastCell).Select
 
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
 
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
 
Thanks, Paul.
Please see my answer to Dave Peterson's posting

Regards
LeoH
 
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.
 
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
 
Ahhh.

I thought you were saying that there was something wrong with Deb's code
(gasp!).
 
You mean "the code on Deb's site" <g>

Dave said:
Ahhh.

I thought you were saying that there was something wrong with Deb's code
(gasp!).
Click to expand...
 
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!
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA set thick borders around non empty cells in range 0
Code for multiple select of cells 16
Excel VBA 1
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Excel Cannot delete empty cells/rows 4
Copy .Areas.Item(1,2,3,4,5, etc.) to a column or a row 5
Last used cell problem 3
Ron de Bruin - Merge cells from all worksheets 0

Back
Top