Delete Rows based on Multiple Cell Criteria

**lukecj** · 02-25-2010, 09:49 AM

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** · 02-25-2010, 10:33 AM

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** · 02-25-2010, 11:31 AM

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** · 02-25-2010, 04:49 PM

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** · 02-25-2010, 11:10 PM

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

**SamT** · 02-26-2010, 03:29 PM

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** · 02-26-2010, 11:02 PM

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

**Bob Phillips** · 02-27-2010, 02:46 AM

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** · 03-03-2010, 08:59 AM

Thanks for your help here. Very much appreciated.

Thread: Delete Rows based on Multiple Cell Criteria

Thread Tools

Display

Delete Rows based on Multiple Cell Criteria

Posting Permissions