clearing out empty range in worksheet (causing headache importing into MSSQL table)

[StephenWallace777]

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.

[#2 · Accepted solution]

@StephenWallace777 

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

[#3]

@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.

[#4]

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.