CODEWITHSUNDEEP
vbaexcelfor-loop
Laur3704
Laur3704

Reputation: 11

Skipping Blank Cells In A VBA For Loop

Hi all this is my first time asking a question on here and it is probably pretty basic to most but this is my first time coding macros. I am working on an excel spread sheet long list of data values and need to delete all of the rows under a certain dollar amount, this part I have working perfectly. But i have blank cells that are on the same line as headers separating groupings that keep getting deleted I need to keep these. Is there a way to skip blank cells in the for loop.

Here is my code:

Private Sub CommandButton1_Click() 

    Dim LastRow As Long, n As Long 

    For n = 1000 To 1 Step -1 

        If Cells(n, 9).Value < 1.01 Then Cells(n, 9).EntireRow.Delete 

    Next n 

End Sub

Upvotes: 0

Views: 964

Answers (2)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Consider:

Private Sub CommandButton1_Click()

    Dim LastRow As Long, n As Long

    For n = 1000 To 1 Step -1

        If Cells(n, 9).Value < 1.01 And Cells(n, 9).Value <> "" Then Cells(n, 9).EntireRow.Delete

    Next n

End Sub

Upvotes: 1

Scott Holtzman
Scott Holtzman

Reputation: 27239

Add a condition in your if statement to ensure the cell is not blank. Like this:

If Len(Cells(n,9)) and Cells(n, 9).Value < 1.01 Then Cells(n, 9).EntireRow.Delete

Upvotes: 3

Related Questions