Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    In an Excel spreadsheeet, you can use Ctrl + End to go to the bottom right-hand corner of the used area of a spreadsheet. You can do the equivalent in VBA using SpecialCells(xlLastCell).

    In the spreadsheet, if you delete (not clear) some previously-used rows or columns at the bottom or right of the remaining data, using Ctrl + End still takes you to the old last cell, until you save the workbook, at which point it resets the last cell to the new bottom-right-hand corner.

    In VBA, if you delete in the same way, xlLastCell likewise continues to take you to the old last cell. I don't want to have to save the spreadsheet at this point, so is there any way I can reset the last cell to the new position without saving?

    Where is my beating-the-head-against-a-brick-wall emoticon when I need it?

    Thanks and regards

    Alison C

  2. #2
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    OK, sorry to waste anyone's time.

    Wasn't sure how long it might take for anyone to notice my query at the weekend, so continued looking. Lo and behold, had the idea of trying to select the Used Range, after which it seems to reset my last used cell as required.

    Thus (in case anyone else wants to know and didn't already):


    lngRow = ActiveCell.SpecialCells(xlLastCell).row 'returns 714

    ActiveSheet.Range(Cells(2,1),Cells(lngRow,1)).Enti reRow.Delete

    lngRow = ActiveCell.SpecialCells(xlLastCell).row 'returns 714

    ActiveSheet.UsedRange.Select

    lngRow = ActiveCell.SpecialCells(xlLastCell).row 'returns 1


    Cheers

    Alison C

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Alison,

    Nice.

    BTW: You can combine the last to lines into: lngRow = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •