VBA - Delete empty rows until there is data

Question

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

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.

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

When deleting rows you should decrement your For loops. This will ensure that all rows will be deleted. Using For Each will ensure that you increment, hence not deleting all. — DDV
– DDV, Commented Nov 25, 2019 at 9:11
How do you plan to distinguish between Empty rows you want to keep and those you want deleted? Would you simply want to delete the empty rows between headers and first data? — JvdV
– JvdV, Commented Nov 25, 2019 at 9:29
Hi JvdV, yes i just want to delete the rows between header and first data. — Vba noob
– Vba noob, Commented Nov 25, 2019 at 9:31

JvdV · Accepted Answer · 2019-11-25 09:48:25Z

1

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.

edited Nov 25, 2019 at 9:48

answered Nov 25, 2019 at 9:37

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vba noob Over a year ago

Hi this works, at first i thought there was an error but it was just because of the worksheet reference. Thank you!

JvdV Over a year ago

Glad it works for you. The worksheet reference is by CodeName. Otherwise you might want to use Worksheets("Sheet1").Range("A:A") @Vbanoob

DDV · Accepted Answer · 2019-11-25 09:15:56Z

1

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

answered Nov 25, 2019 at 9:15

DDV

2,4155 gold badges16 silver badges37 bronze badges

Comments

Davesexcel · Accepted Answer · 2019-11-25 09:18:00Z

1

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

answered Nov 25, 2019 at 9:18

Davesexcel

6,9922 gold badges31 silver badges44 bronze badges

1 Comment

Vba noob Over a year ago

Hi, by running your code, only the first line of each data chunk is left, what i want is actually just to remove the lines between the header and the first row of data. The remaining empty lines should remain there.

piele · Accepted Answer · 2019-11-25 09:44:37Z

1

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.

edited Nov 25, 2019 at 9:44

answered Nov 25, 2019 at 9:21

piele

786 bronze badges

2 Comments

Vba noob Over a year ago

Hi, by running your code, only the first line of each data chunk is left, what i want is actually just to remove the lines between the header and the first row of data. the remaining empty lines should remain there.

piele Over a year ago

I've edited the previous code to match your description. The solution is to count how many blank rows you have underneath row 1, delete afterwards.

Collectives™ on Stack Overflow

VBA - Delete empty rows until there is data

4 Answers 4

2 Comments

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related