Delete a row if one column says "x" or "y" and the other column is blank

[VBAProIWish]

Hello All,

Here's my question...
If the cell in the column named "location" says "Europe" or "Canada", and the cell in the column (in the same row) named "office" is blank, delete the entire row.



A couple of things to note....

1. There will be many blank cells, so I would need the search to continue and only stop at the bottommost row with data in column header named "report" as this is always the longest column with contiguous data.

2. The columns may switch around, so I would like the searches to find the cells based on their column header name, not column position.

3. I would like all search criteria to be case iNsEnsiTIve.



Thanks much!

 

[wideboydixon]

Give this a try:

Public Sub VBAProICouldBe()

Dim reportColumn As Long
Dim locationColumn As Long
Dim officeColumn As Long
Dim lastRow As Long
Dim thisRow As Long

Application.ScreenUpdating = False

reportColumn = Application.Match("report", Range("1:1"), 0)
locationColumn = Application.Match("location", Range("1:1"), 0)
officeColumn = Application.Match("office", Range("1:1"), 0)
lastRow = Cells(Rows.Count, reportColumn).End(xlUp).Row
thisRow = 2
Do While thisRow <= lastRow
    If (LCase(Cells(thisRow, locationColumn).Value) = "europe" _
    Or LCase(Cells(thisRow, locationColumn).Value) = "canada") _
    And Trim(Cells(thisRow, officeColumn).Value) = "" Then
        Cells(thisRow, "A").EntireRow.Delete
        lastRow = lastRow - 1
    Else
        thisRow = thisRow + 1
    End If
Loop

Application.ScreenUpdating = True

End Sub

WBD

 

[JLGWhiz]

Sub t()
Dim col1 As Long, col2 As Long, col3 As Long
With ActiveSheet
    col1 = .Range("1:1").Find("location", , xlValues).Column
    col2 = .Range("1:1").Find("office", , xlValues).Column
    col3 = .Range("1:1").Find("report", , xlValues).Column
    .UsedRange.AutoFilter col1, "=Europe", xlOr, "=Canada"
    For i = .Cells(Rows.Count, col3).End(xlUp).Row To 2 Step -1
        If .Rows(i).Hidden = False Then
            If .Cells(i, col2) = "" Then Rows(i).Delete
        End If
    Next
    .AutoFilterMode = False
End With
End Sub

 

[VBAProIWish]

That did the trick!

Thanks much!

 

[JLGWhiz]

Glad we could help,
regards, JLG