CODEWITHSUNDEEP
vbaexcel
Samfraser
Samfraser

Reputation: 3

How to select all empty rows in a range and delete them?

I'm currently using this code to do look for empty cells in column L and if this cell is empty for the code to delete all data in that row between K and S. It works fine until I get to a consecutive blank row; when the code deletes the first row the code then moves to the next i but due to the shift it skips a blank line. (apologies for bad explanation)

Sub deleteempty2()
With Sheets("baml")

last2 = .Range("L" & Rows.Count).End(xlUp).Row

For i = 5 To last2
If .Cells(i, 12) = "" Then
    .Range("k" & i & ":s" & i).delete Shift:=xlUp
    last1 = last1 - 1
Else: End If
Next i


End With


End Sub

I tried including do until L5 <> "" followed with a loop, but this seemed to enter a perpetual loop.

Any suggestions welcome :)

Upvotes: 0

Views: 1663

Answers (2)

Xabier
Xabier

Reputation: 7735

When deleting it is best to do it from the last row to the first, as you mentioned rows/cells shift up, by going from bottom to top, you won't miss any, as below:

Sub deleteempty2()
    With Sheets("baml")
        last2 = .Range("L" & Rows.Count).End(xlUp).Row

        For i = last2 To 5 Step -1
            If .Cells(i, "L") = "" Then
                .Range("K" & i & ":S" & i).Delete Shift:=xlUp
            End If
        Next i
    End With
End Sub

Upvotes: 3

Chris
Chris

Reputation: 124

A more efficient way to do it (from my point of view), without a For loop:

Dim ws As Worksheet
Dim rg As Range

Set ws = ThisWorkbook.Worksheets("baml")
Set rg = ws.Range("A1:C405") 'whatever the range you want

rg.Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

I do that all the time and it works fine!

Upvotes: 0

Related Questions