Resetting Last Cell after deleting

Topic

New Reply

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

Reply | Quote

Replies

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)).EntireRow.Delete

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

ActiveSheet.UsedRange.Select

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

Cheers

Alison C

Reply | Quote

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!
Computer Specs

Reply | Quote