Forum Discussion
clearing out empty range in worksheet (causing headache importing into MSSQL table)
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-0fc3-4f69-8038-b3c1d86e99e9) 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.
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 Replies
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 SubSubodh_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.
