Delete Rows based on Multiple Cell Criteria

[lukecj]

The code I am working with is:


Sub GSFIDO()
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("E7:E29"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) < 100000 Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete
End Sub
Currently, you'll see that the range is fixed (E7:E29). What I would like to do is find a way to search the rows if the cell in column E is below 10,000 and the cell in column A matches a particular name. In this way, the range will not have to be fixed and I do not have to modify the macro when a new account is added and the range changes. Something Like this...


Sub GSFIDO()
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("E7",Range("E65536").End(xlUp)), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) < 100000 and (cell.Value *in column A*) = "Goldman Sachs" Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete
End Sub
Thanks in advance for the help and knowledge.

[Bob Phillips]

Sub GSFIDO()
Dim LastRow As Long
Dim rng As Range
Dim del As Range
Dim cell As Range
LastRow = Cells(Rows.Count, "E").End(xlUp).Row
Set rng = Range("E6").Resize(LastRow - 5)
rng.AutoFilter Field:=1, Criteria1:="<10000"
On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
For Each cell In rng
If cell.Offset(0, -4) Like "*Goldman Sachs*" Then
If del Is Nothing Then
Set del = cell
Else
Set del = Union(del, cell)
End If
End If
Next cell
If Not del Is Nothing Then del.EntireRow.Delete
Range("E6").AutoFilter
End Sub

[lukecj]

Thanks for the help there. Unfortunately, that only works if I specify one combination. I need it to be able to work for multiple combinations. Is there a way to do it without using Auto Filter? See the code below...I get an error with multiple combinations. You're awesome for giving me a hand. Tks...


Sub GSFIDO()
Dim LastRow As Long
Dim rng As Range
Dim del As Range
Dim cell As Range
LastRow = Cells(Rows.Count, "E").End(xlUp).Row
Set rng = Range("E6").Resize(LastRow - 5)
rng.AutoFilter Field:=1, Criteria1:="<10000"
On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
For Each cell In rng
If cell.Offset(0, -4) Like "SRM CASH ACCOUNT" _
Or "MEI., CASH ACCOUNT" _
Or "JDC- CASH ACCOUNT" _
Or "JM - CASH ACCOUNT" _
Or "RR - CASH ACCOUNT" _
Or "NNCP - CASH/CHECKING ACCOUNT" _
Or "COM - CASH ACCOUNT" _
Or "LB - CASH/CHECKING ACCOUNT" _
Or "SS - FIXED INCOME ACCOUNT" _
Or "BAP/CHECKING ACCOUNT" _
Or "RCR. - MUNI CASH ACCOUNT" _
Or "MEL- CASH ACCOUNT" _
Or "FFB - FOX CASH ACCOUNT" _
Or "SRM - DCC ACCOUNT" _
Or "AA - CASH ACCOUNT" _
Or "DHB 03-15-99- CASH ACCOUNT" _
Or "LHS,S&S FIXED INCOME ACCOUNT" _
Or "P - CASH ACCOUNT" _
Or "LB - CASH ACCOUNT" _
Or "RM - CASH ACCOUNT" _
Or "JM - CASH ACCOUNT" Then
If del Is Nothing Then
Set del = cell
Else
Set del = Union(del, cell)
End If
End If
Next cell
If Not del Is Nothing Then del.EntireRow.Delete
Range("E6").AutoFilter
End Sub

[Bob Phillips]

You have to spell them all out


For Each cell In Rng
If cell.Offset(0, -4) = "SRM CASH ACCOUNT" _
Or cell.Offset(0, -4) = "MEI., CASH ACCOUNT" _
Or cell.Offset(0, -4) = "JDC- CASH ACCOUNT" _
Or cell.Offset(0, -4) = "JM - CASH ACCOUNT" _
Or cell.Offset(0, -4) = "RR - CASH ACCOUNT" _
Or cell.Offset(0, -4) = "NNCP - CASH/CHECKING ACCOUNT" _
Or cell.Offset(0, -4) = "COM - CASH ACCOUNT" _
Or cell.Offset(0, -4) = "LB - CASH/CHECKING ACCOUNT" _
Or cell.Offset(0, -4) = "SS - FIXED INCOME ACCOUNT" _
Or cell.Offset(0, -4) = "BAP/CHECKING ACCOUNT" _
Or cell.Offset(0, -4) = "RCR. - MUNI CASH ACCOUNT" _
Or cell.Offset(0, -4) = "MEL- CASH ACCOUNT" _
Or cell.Offset(0, -4) = "FFB - FOX CASH ACCOUNT" _
Or cell.Offset(0, -4) = "SRM - DCC ACCOUNT" _
Or cell.Offset(0, -4) = "AA - CASH ACCOUNT" _
Or cell.Offset(0, -4) = "DHB 03-15-99- CASH ACCOUNT" _
Or cell.Offset(0, -4) = "LHS,S&S FIXED INCOME ACCOUNT" _
Or cell.Offset(0, -4) = "P - CASH ACCOUNT" _
Or cell.Offset(0, -4) = "LB - CASH ACCOUNT" _
Or cell.Offset(0, -4) = "RM - CASH ACCOUNT" _
Or cell.Offset(0, -4) = "JM - CASH ACCOUNT" Then

[lukecj]

Right...stupid, glaring mistake. Thanks so much for your help.

[SamT]

Turn it around for speed


Option Base 1
Dim CheckArray(21) as String
CheckArray(1) = "SRM CASH ACCOUNT"
CheckArray(2) = "MEI., CASH ACCOUNT"
CheckArray(3) = "JDC- CASH ACCOUNT"
CheckArray(4) = "JM - CASH ACCOUNT"
CheckArray(5) = "RR - CASH ACCOUNT"
CheckArray(6) = "NNCP - CASH/CHECKING ACCOUNT"
CheckArray(7) = "COM - CASH ACCOUNT"
CheckArray(8) = "LB - CASH/CHECKING ACCOUNT"
CheckArray(9) = "SS - FIXED INCOME ACCOUNT"
CheckArray(10) = "BAP/CHECKING ACCOUNT"
CheckArray(11) = "RCR. - MUNI CASH ACCOUNT"
CheckArray(12) = "MEL- CASH ACCOUNT"
CheckArray(13) = "FFB - FOX CASH ACCOUNT"
CheckArray(14) = "SRM - DCC ACCOUNT"
CheckArray(15) = "AA - CASH ACCOUNT"
CheckArray(16) = "DHB 03-15-99- CASH ACCOUNT"
CheckArray(17) = "LHS,S&S FIXED INCOME ACCOUNT"
CheckArray(18) = "P - CASH ACCOUNT"
CheckArray(19) = "LB - CASH ACCOUNT"
CheckArray(20) = "RM - CASH ACCOUNT"
CheckArray(21) = "JM - CASH ACCOUNT"
'...
CellValue = cell.Offset(0, -4).Value
For i = 1 to CheckArray.Count
If Checkarray(i) Like CellValue
'...


That way, if you have to do this often, but with different values to check for, you can pass fill the array outside the procedure and just pass it in as an argument.

[lukecj]

Thanks. That's really sweet. I appreciate the tip. Always amazed at how many different approaches there are.

[Bob Phillips]

If you use an array, you can use a worksheet function to avoid that extra lopp


Dim CheckArray(21) As String
CheckArray(1) = "SRM CASH ACCOUNT"
CheckArray(2) = "MEI., CASH ACCOUNT"
CheckArray(3) = "JDC- CASH ACCOUNT"
CheckArray(4) = "JM - CASH ACCOUNT"
CheckArray(5) = "RR - CASH ACCOUNT"
CheckArray(6) = "NNCP - CASH/CHECKING ACCOUNT"
CheckArray(7) = "COM - CASH ACCOUNT"
CheckArray(8) = "LB - CASH/CHECKING ACCOUNT"
CheckArray(9) = "SS - FIXED INCOME ACCOUNT"
CheckArray(10) = "BAP/CHECKING ACCOUNT"
CheckArray(11) = "RCR. - MUNI CASH ACCOUNT"
CheckArray(12) = "MEL- CASH ACCOUNT"
CheckArray(13) = "FFB - FOX CASH ACCOUNT"
CheckArray(14) = "SRM - DCC ACCOUNT"
CheckArray(15) = "AA - CASH ACCOUNT"
CheckArray(16) = "DHB 03-15-99- CASH ACCOUNT"
CheckArray(17) = "LHS,S&S FIXED INCOME ACCOUNT"
CheckArray(18) = "P - CASH ACCOUNT"
CheckArray(19) = "LB - CASH ACCOUNT"
CheckArray(20) = "RM - CASH ACCOUNT"
CheckArray(21) = "JM - CASH ACCOUNT"
CellValue = cell.Offset(0, -4).Value
If Not IsError(Application.Match(CellValue, CheckArray(i), 0)) Then
'...

[lukecj]

Thanks for your help here. Very much appreciated.