Auto Deleting empty rows

[SharonInGa]

Is there code or functions that looks for rows without
data and automatically deletes that row.

 

[Kevin H. Stecyk]

SharonInGa wrote...

Is there code or functions that looks for rows without
data and automatically deletes that row.

Hi Sharon,

Press Alt F11. Insert Module.

Copy and paste....

Sub DeleteRows()
Dim rnRange As Range

Set rnRange = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
Set rnRange = rnRange.SpecialCells(xlBlanks)
rnRange.EntireRow.Delete
End Sub

Go back to normal Excel, press Alt F8, and select DeleteRows.

The above routine uses Column A as its guide. Adapt if necessary to a
different column.

Hope that helps.

Regards,
Kevin

 

[Gord Dibben]

Sharon

Another option......deletes rows only if entire row is blank.

Sub DeleteEmptyRows()
''only if entire row is blank
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For R = LastRow To 1 Step -1
If Application.CountA(Rows(R)) = 0 Then Rows(R).Delete
Next R
End Sub

Gord Dibben Excel MVP

 

[Kevin H. Stecyk]

Hi Gord,

I saw your solution but didn't understand it at first. I don't use VBA
frequently enough to be able to quickly grasp new routines. So I played
with your excellent routine to learn and understand.

I changed it a bit to the following as I was playing with it.

Sub DeleteEmptyRows()
'\ only if entire row is blank

Dim lStartRow As Long
Dim lUsedRows As Long
Dim lEndRow As Long
Dim lCounter As Long

Application.ScreenUpdating = False

lStartRow = ActiveSheet.UsedRange.Row - 1
lUsedRows = ActiveSheet.UsedRange.Rows.Count
lEndRow = lStartRow + lUsedRows

For lCounter = lEndRow To lStartRow Step -1
If Application.CountA(Rows(lCounter)) = 0 Then Rows(lCounter).Delete
Next lCounter

End Sub

Do you see any bugs in it? Where I think it might be a slight improvement
over your earlier version is your loop. You loop back from the final row to
"1". But if the data doesn't begin at row1, there is no need to go back to
it.

I suspect this is probably pendantic, as it will go through the beginning
blank lines very quickly anyway. I had to "break it down" so I properly
understood how your LastRow worked. Once I did that, I just changed your
routine slightly.

If you think this is okay, I will be adding your routine to my "library" of
routines. It seems to work fine in my little test trials.

Thank you Gord.

Best regards,
Kevin




Gord Dibben...

 

[Kevin H. Stecyk]

Gord,

I understand your loop better now. For some reason I was thinking of
eliminating the blank rows *within* the data. That's what the modified
routine does.

Your original routine correctly eliminates all blank rows.

If there is anything else you quickly spot with the modified version, please
let me know.

Best regards,
Kevin