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

--

Vasant


"Elsie" wrote in message
...
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

Thanks.... but just curious.... how do I select the last cell?

Elsie

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
ActiveSheet.PageSetup.PrintArea = ActiveSheet.UsedRange.Address

--

Vasant


"Elsie" wrote in message
...
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

Hi Elsie

One way:

ActiveSheet.Cells.SpecialCells(xlLastCell).Select

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Elsie" skrev i en meddelelse
...
Thanks.... but just curious.... how do I select the last cell?

Elsie

And sometimes hitting ctrl-end or using Leo's code will go further down or to
the right than you expected.

You can try to reset the last cell by using techniques at Debra Dalgleish's
site:
http://www.contextures.com/xlfaqApp.html#Unused



Elsie wrote:

Thanks.... but just curious.... how do I select the last cell?

Elsie

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
ActiveSheet.PageSetup.PrintArea = ActiveSheet.UsedRange.Address

--

Vasant


"Elsie" wrote in message
...
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





--

Dave Peterson

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

"Leo Heuser" wrote in message
...
Hi Elsie

One way:

ActiveSheet.Cells.SpecialCells(xlLastCell).Select

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Elsie" skrev i en meddelelse
...
Thanks.... but just curious.... how do I select the last cell?

Elsie

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

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Dave Peterson" skrev i en meddelelse
...
And sometimes hitting ctrl-end or using Leo's code will go further down or
to
the right than you expected.

You can try to reset the last cell by using techniques at Debra
Dalgleish's
site:
http://www.contextures.com/xlfaqApp.html#Unused

Thanks, Paul.
Please see my answer to Dave Peterson's posting

Regards
LeoH


"Paulw2k" skrev i en meddelelse
...
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

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.



Leo Heuser wrote:

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

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Dave Peterson" skrev i en meddelelse
...
And sometimes hitting ctrl-end or using Leo's code will go further down or
to
the right than you expected.

You can try to reset the last cell by using techniques at Debra
Dalgleish's
site:
http://www.contextures.com/xlfaqApp.html#Unused


--

Dave Peterson

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


"Dave Peterson" skrev i en meddelelse
...
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.



Leo Heuser wrote:

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

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Dave Peterson" skrev i en meddelelse
...
And sometimes hitting ctrl-end or using Leo's code will go further
down or
to
the right than you expected.

You can try to reset the last cell by using techniques at Debra
Dalgleish's
site:
http://www.contextures.com/xlfaqApp.html#Unused


--

Dave Peterson

Ahhh.

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



Leo Heuser wrote:

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

"Dave Peterson" skrev i en meddelelse
...
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.



Leo Heuser wrote:

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

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Dave Peterson" skrev i en meddelelse
...
And sometimes hitting ctrl-end or using Leo's code will go further
down or
to
the right than you expected.

You can try to reset the last cell by using techniques at Debra
Dalgleish's
site:
http://www.contextures.com/xlfaqApp.html#Unused


--

Dave Peterson


--

Dave Peterson

You mean "the code on Deb's site" <g

Dave Peterson wrote:
Ahhh.

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



Leo Heuser wrote:

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

"Dave Peterson" skrev i en meddelelse
...

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.



Leo Heuser wrote:

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

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Dave Peterson" skrev i en meddelelse
...

And sometimes hitting ctrl-end or using Leo's code will go further

down or

to

the right than you expected.

You can try to reset the last cell by using techniques at Debra

Dalgleish's

site:
http://www.contextures.com/xlfaqApp.html#Unused


--

Dave Peterson





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Yes, see my page
http://www.mvps.org/dmcritchie/excel...l.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" wrote in message ...
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


"Dave Peterson" skrev i en meddelelse
...
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.



Leo Heuser wrote:

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

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Dave Peterson" skrev i en meddelelse
...
And sometimes hitting ctrl-end or using Leo's code will go further
down or
to
the right than you expected.

You can try to reset the last cell by using techniques at Debra
Dalgleish's
site:
http://www.contextures.com/xlfaqApp.html#Unused


--

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!



Debra Dalgleish wrote:

You mean "the code on Deb's site" <g

Dave Peterson wrote:
Ahhh.

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



Leo Heuser wrote:

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

"Dave Peterson" skrev i en meddelelse
...

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.



Leo Heuser wrote:

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

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Dave Peterson" skrev i en meddelelse
...

And sometimes hitting ctrl-end or using Leo's code will go further

down or

to

the right than you expected.

You can try to reset the last cell by using techniques at Debra

Dalgleish's

site:
http://www.contextures.com/xlfaqApp.html#Unused


--

Dave Peterson




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

--

Dave Peterson

Thanks, David!

Regards
LeoH


"David McRitchie" skrev i en meddelelse
...
Yes, see my page
http://www.mvps.org/dmcritchie/excel...l.htm#resetall
It is based on a John Walkenbach's Tip 53
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]