Forum Discussion
clearing out empty range in worksheet (causing headache importing into MSSQL table)
Ctrl+End will still detect the earlier last used cell on the sheet unless you save your file after deleting the rows.
First create a backup of your file and then run this macro and see if your issue gets resolved.
Sub DeleteEmptyRows() Dim ws As Worksheet Dim lr As Long Dim elr As Long Dim sRow As Long Application.ScreenUpdating = False Set ws = ActiveSheet 'Set the Worksheet as per your requirement, currenty it is set to the ActiveSheet. On Error Resume Next lr = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row elr = ws.Cells.SpecialCells(xlCellTypeLastCell).Row On Error GoTo 0 If lr > 0 And lr <> elr Then sRow = lr + 1 ws.Rows(sRow & ":" & elr).ClearFormats ws.Rows(sRow & ":" & elr).EntireRow.Delete ThisWorkbook.Save End If Application.ScreenUpdating = True End Sub
Ctrl+End will still detect the earlier last used cell on the sheet unless you save your file after deleting the rows.
First create a backup of your file and then run this macro and see if your issue gets resolved.
Sub DeleteEmptyRows()
Dim ws As Worksheet
Dim lr As Long
Dim elr As Long
Dim sRow As Long
Application.ScreenUpdating = False
Set ws = ActiveSheet 'Set the Worksheet as per your requirement, currenty it is set to the ActiveSheet.
On Error Resume Next
lr = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
elr = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
On Error GoTo 0
If lr > 0 And lr <> elr Then
sRow = lr + 1
ws.Rows(sRow & ":" & elr).ClearFormats
ws.Rows(sRow & ":" & elr).EntireRow.Delete
ThisWorkbook.Save
End If
Application.ScreenUpdating = True
End Sub
Subodh_Tiwari_sktneer , thank you for this. Indeed, I had not saved after deleting rows (only saved after the clear all suggestion of the earlier post I mentioned). Deleting rows and saving solved the problem. And very nice Macro! I noticed I still had to save the worksheet after the macro finished. Even adding an additional "ThisWorkbook.Save" just before the End Sub didn't prevent CTRL + END from finding the end of that old empty range. Once I saved manually, the sheet was fine and the phantom range was gone.
You're welcome StephenWallace777! Glad this resolved your issue.
Please take a minute to accept the post with the proposed solution as a Best Response to mark your question as Solved.
