Excel VBA hide rows based on two different cell values

[Stuepef]

I have a tab within my workbook that has a list of client details and I am looking to delete the rows that don't match two different cell values. Here are the details:

The client details data is on Sheet9 Column A starting at row 5.

The cell values are Sheet1 cell C2 and Sheet10 cell A1.

I want the code to delete all rows on Sheet9 that do not match cell C2 or cell A1.

Thank you!

 

[lrobbo314]

On Sheet9, what column are the values in that have to match the values from C2 and A1?

 

[Stuepef]

@Irobbo314 - All of the data is in Column A starting at row 5 (cell A5)

 

[lrobbo314]

Try this code. Try it on a copy of your workbook because there is no 'Undo' with this stuff.

Sub DeleteRows()
Dim ws As Worksheet: Set ws = Sheets("Sheet9")
Dim LR As Long: LR = ws.Range("A" & Rows.Count).End(xlUp).Row
Dim Crit1 As Variant: Crit1 = Sheets("Sheet1").Range("C2").Value
Dim Crit2 As Variant: Crit2 = Sheets("Sheet10").Range("A1").Value


For i = LR To 5 Step -1
    If ws.Cells(LR, 1).Value <> Crit1 And ws.Cells(LR, 1).Value <> Crit2 Then
        ws.Rows(LR).Delete
    End If
Next i


End Sub

 

[lrobbo314]

Sorry, made a mistake in last code.

Sub DeleteRows()
Dim ws As Worksheet: Set ws = Sheets("Sheet6")
Dim LR As Long: LR = ws.Range("A" & Rows.Count).End(xlUp).Row
Dim Crit1 As Variant: Crit1 = Sheets("Sheet1").Range("C2").Value
Dim Crit2 As Variant: Crit2 = Sheets("Sheet10").Range("A1").Value


For i = LR To 5 Step -1
    If ws.Cells(i, 1).Value <> Crit1 And ws.Cells(i, 1).Value <> Crit2 Then
        ws.Rows(i).Delete
    End If
Next i


End Sub

 

[Stuepef]

I receive a run-time error 9 - subscript out of range. It highlights the set ws = Sheets("Sheet6") part. Is the code using code names?

 

[Stuepef]

@Fluff - can this code mimic what you did here except change the cell values to not equal to C2 & A1?

[COLOR=#333333]Sub FilterDelete()[/COLOR][COLOR=#333333][INDENT]   Dim Usdrws As Long
   
   Application.ScreenUpdating = False
   With sheet13
      Usdrws = .Range("A" & Rows.Count).End(xlUp).Row
      .Range("A1").AutoFilter 1, "<>" & sheet2.Range("H1").Value
      .Range("A2:A" & Usdrws).SpecialCells(xlVisible).EntireRow.Delete
      .Range("A1").AutoFilter
   End With
End Sub
[/INDENT]

[/COLOR]

 

[Fluff]

How about

Sub Fltr()
   Dim Lr As Long
   
   Lr = Sheet9.Range("A" & Rows.Count).End(xlUp).Row
   Sheet9.Range("A4:A" & Lr).AutoFilter 1, "<>" & Sheet1.Range("C2"), xlAnd, "<>" & Sheet10.Range("A1")
   Sheet9.Range("A5:A" & Lr).SpecialCells(xlVisible).EntireRow.Delete
End Sub

 

[Stuepef]

@Fluff - the code deletes all of the entries that dont match correctly, but the rows that match C2 and A1 are hidden instead of visible. If I click on the filter, it doesn't have anything selected, but does have the entries that match within the filter only. Hard to explain..

 

[Fluff]

How about

Sub Fltr()
   Dim Lr As Long
   
   Lr = Sheet9.Range("A" & Rows.Count).End(xlUp).Row
   Sheet9.Range("A4:A" & Lr).AutoFilter 1, "<>" & Sheet1.Range("C2"), xlAnd, "<>" & Sheet10.Range("A1")
   Sheet9.Range("A5:A" & Lr).SpecialCells(xlVisible).EntireRow.Delete
   Sheet9.AutoFilterMode = False
End Sub