Delete rows on values from 2 columns - code sample included

[VBAProIWish]

Hello All,

I am trying to create a macro that deletes a row based on values in two columns.

Because my column positions are dynamic, they must be found by column (header) name (Customer Number, Total, Order Size)

I thought the code below would do it, but I am getting the error..."Compile Error - End if without block if"

I'm sure it's one silly thing that I just can't figure out and any help would be much appreciated!

[B]Delete any row where the "Order Size" column is small and the "Total" column is less than $500000[/B]
Dim customerCol As Integer
Dim totalCol As Integer
Dim bigorderCol As Integer
 
customerCol = Application.WorksheetFunction.Match("Customer Number", Rows(1).EntireRow, 0)
totalCol = Application.WorksheetFunction.Match("Total", Rows(1).EntireRow, 0)
bigorderCol = Application.WorksheetFunction.Match("Order Size", Rows(1).EntireRow, 0)
 
'Select the last row of customer numbers
Cells(65536, customerCol).End(xlUp).Select
 
'check and loop until back at the top of the sheet
Do While ActiveCell.Row > 1
    If Cells(ActiveCell.Row, totalCol).Value < 500000 _
    And Cells(ActiveCell.Row, bigorderCol).Value = "Small" _
    Then Cells.EntireRow.Delete
    End If
 
    ActiveCell.Offset(-1, 0).Select
 
Loop
 
End Sub

 

[Fluff]

Just delete the End If, you have a single line If so it should not have an End If

 

[Joe4]

You have you whole IF/THEN written as one single line (since you are using line continuation characters).
If you have the IF and THEN on the same line, then you do NOT use and END IF statement, i.e.

    If Cells(ActiveCell.Row, totalCol).Value < 500000 _
    And Cells(ActiveCell.Row, bigorderCol).Value = "Small" _
    Then Cells.EntireRow.Delete

If you want to use the END IF, then remove the underscore after "Small", i.e.

    If Cells(ActiveCell.Row, totalCol).Value < 500000 _
    And Cells(ActiveCell.Row, bigorderCol).Value = "Small" Then
    Cells.EntireRow.Delete
    End If

By the way, you usually do not need to select cells in VBA in order to work with them. Doing so actually slows down your code.
I would actually replace this whole section of code:

'Select the last row of customer numbers
Cells(65536, customerCol).End(xlUp).Select

'check and loop until back at the top of the sheet
Do While ActiveCell.Row > 1
    If Cells(ActiveCell.Row, totalCol).Value < 500000 _
    And Cells(ActiveCell.Row, bigorderCol).Value = "Small" _
    Then Cells.EntireRow.Delete
    End If
 
    ActiveCell.Offset(-1, 0).Select
 
Loop

with this:

    Dim lr As Long
    Dim r As Long
 
'   Find last row with customer numbers
    lr = Cells(Rows.count, customerCol).End(xlUp).Row
 
    Application.ScreenUpdating = False
 
'   Loop through all rows
    For r = lr To 2 Step -1
        If Cells(r, totalCol).Value < 500000 _
            And Cells(r, bigorderCol).Value = "Small" Then
            Rows(r).Delete
        End If
    Next r
 
    Application.ScreenUpdating = True

 

[VBAProIWish]

What can I say?
You guys are truly a wealth of knowledge!
This completely solved my problem!

Thanks so much!

 

[Fluff]

Glad we could help & thanks for the feedback.