CODEWITHSUNDEEP
excelvba
Vba noob
Vba noob

Reputation: 164

VBA - Delete empty rows until there is data

I have a data that looks like this, the amount of empty rows might increase or decrease and is not fixed.

enter image description here

What I want to do is to turn it to this, to delete the empty row until there is data and make it stop. I do not want it to remove other empty rows that might be in the middle of the data.

enter image description here

I have been testing on something and this is what I have, however this code does not clear all the empty rows, but only a few. I realized that the problem might be using the for each cell in rng way, as it will continue to the next cell when I use cell.entirerow.delete, but I might be wrong.

Sub Test()
    Dim cell as range, rng as range
    Dim lRow as long

    Set rng = Range("C3:C" & lRow)

    For Each cell In rng
        If IsEmpty(cell.Offset(-1)) Then
            cell.Offset(-1).EntireRow.Delete
        Else
            Exit For
        End If
    Next cell
End Sub

Thank you for helping out

Upvotes: 3

Views: 3118

Answers (4)

JvdV
JvdV

Reputation: 75850

As per your comment you are interested in keeping the empty rows further down. Only those between headers and the first data needs to go. Therefor you are actually interested in the first cell containing data. Also, instead of deleting one-by-one you can delete a range of rows at once.

Try the following:

Sub Test()

Dim fr As Long

With Sheet1.Range("A:A")
   fr = .Find(what:="*", after:=.Cells(1, 1), LookIn:=xlValues).Row
   If fr > 2 Then
       .Rows("2:" & fr - 1).Delete
   End If
End With

End Sub

This does the job for your sample data.

Upvotes: 1

piele
piele

Reputation: 78

You're encountering a classic issue. The challenge is; If you delete row 26, the row which previously were 27, will be 26. Therefore, the next loop will skip it and you're left with (potentially) blank rows.

One way to cope with this is to do a backwards loop. My solution is:

Sub Test()
    Dim lRow As Integer

    For i = 2 To 100
        If IsEmpty(Cells(i, 1)) Then
            lRow = i
        Else
            Exit For
        End If
    Next i

    For i = lRow To 2 Step -1
        If IsEmpty(Cells(i, 1)) Then
            Cells(i, 1).EntireRow.Delete
        End If
    Next i

End Sub

This solves the issue, since if you delete row 26, the previous row 27 will become row 26. But this change doesn't matter, because the loop now goes from bottom to top, meaning the next loop checks row 25.

Upvotes: 1

Davesexcel
Davesexcel

Reputation: 6984

Start from the bottom and work up.

Sub Button1_Click()
    Dim LstRw As Long, x

    LstRw = Cells(Rows.Count, "C").End(xlUp).Row

    For x = LstRw To 1 Step -1
        If IsEmpty(Cells(x, 2)) Then Cells(x, 2).EntireRow.Delete
    Next

End Sub

Most instances this simple code below will work

You need error trap if no blanks are found

Sub delrws()
    On Error Resume Next
    Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Upvotes: 1

DDV
DDV

Reputation: 2384

Following on my comment RE decrementing your For loop. If you do something like the below it should work:

Option Explicit

Sub Test()

    Dim i As Long, lRow As Long
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet1")

    lRow = ws.Range("C" & ws.Rows.Count).End(xlUp).Row

    For i = lRow To 1 Step -1
        If ws.Cells(i, 3).Value = "" And ws.Cells(i - 1, 3).Value = "" Then
            ws.Cells(i, 3).EntireRow.Delete
        End If
    Next i

End Sub

Upvotes: 1

Related Questions