Apr 01 2021 09:41 AM
I have a workbook with some sheet tabs that have a large range of empty cells that I cannot remove. Using CTRL + END takes me to an odd location, well below my existing data. I tried deleting rows below my data. That made no difference
I found a post on this forum (https://support.microsoft.com/en-us/office/locate-and-reset-the-last-cell-on-a-worksheet-c9e468a8-0f...) that I thought would solve my problem, but it did not work as advertised. selecting the empty range, clearing all, saving, closing and reopening the file, and I sill have that empty range.
This is particularly troublesome when using the data import wizard, to pull a sheet into MS SQL. I end up with an overly large table with mostly NULLs.
I am using Excel (Office) 10 over Windows, running on Windows Server 2008.
Apr 01 2021 11:30 AM
Solution
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
Apr 01 2021 12:27 PM
@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.
Apr 01 2021 07:59 PM
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.
Apr 01 2021 11:30 AM
Solution
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