Last cell
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 |
Last cell
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 |
Last cell
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 area.... Elsie |
Last cell
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 |
Last cell
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 area.... Elsie -- Dave Peterson |
Last cell
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 |
Last cell
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 |
Last cell
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 |
Last cell
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 |
Last cell
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 |
Last cell
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 |
Last cell
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 |
Last cell
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 |
Last cell
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 |
Last cell
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] |
All times are GMT +1. The time now is 02:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com