How to use a If statement within a for loop in VBA?

Question

In column D, I am trying to delete every row that has a value in it. If the cell is blank, it is not deleted

The For loop only looks at D2 and deletes the entire row because it has a value in it. The loop then stops, and does not continue. The NumRows value is 9324 because there are that many rows in column A.

I'm stuck and cannot figure out how to get the loop to continue. Thanks!

Dim VarDeleteLoop as long
Dim NumRows As Long


NumRows = Worksheets("Sheet").Cells(Application.Rows.Count, "A").End(xlUp).Row


For VarDeleteLoop = 2 To NumRows

        Cells(VarDeleteLoop, 4).Select

        If Cells(VarDeleteLoop, 4).Value = "" Then
                Cells(VarDeleteLoop, 4).Select
        Else
                Cells(VarDeleteLoop, 4).Select
                Selection.EntireRow.Delete
        End If

Next VarDeleteLoop

When the loop stops, do you get an error message? If so, what is it? Also, you should change Application.Rows.Count to Worksheets("Sheet").Rows.Count — Mistella
– Mistella, Commented May 31, 2018 at 21:33
I actually don't get any error. It just deletes the rows and ends. — kh4332
– kh4332, Commented May 31, 2018 at 21:40

Sam · Accepted Answer · 2018-05-31 21:46:10Z

4

When removing things, it is often a better idea to do it backwards. That way you don't need to keep track of the number of the next line, if the previous one was deleted.

Do your for loop like this instead:

For VarDeleteLoop = NumRows To 2 Step -1

answered May 31, 2018 at 21:46

Sam

5,7411 gold badge21 silver badges36 bronze badges

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

1 Comment

kh4332 Over a year ago

That worked! I'm new to VBA, only just started working in it about 2 weeks ago, so still have a lot of learning to do. Thanks for your help!

user4039065 · Accepted Answer · 2018-05-31 23:55:51Z

1

.AutoFilter can quickly isolate non-blank cells and identify them for deletion.

With Worksheets("sheet").Columns("D").Cells
    If .Parent.AutoFilterMode Then .Parent.AutoFilterMode = False
    .AutoFilter field:=1, Criteria1:="<>"
    With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
        If CBool(Application.Subtotal(103, .Cells)) Then
            .SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End If
    End With
    .Parent.AutoFilterMode = False
End With

answered May 31, 2018 at 23:55

user4039065

Collectives™ on Stack Overflow

How to use a If statement within a for loop in VBA?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related