Delete all rows in a column that don't match a value in another worksheet using VBA

Question

I am learning VBA and am having trouble trying to develop a macro in a workbook with two worksheets. The first worksheet is named as "Para" and the second is named "Report".

When I enter a number or text into Para!C6 (e.g. 76894), I would like the VBA code to delete all rows in column Report!Y:Y except the one which matches the value in Para!C6.

I tried the following code, but it is not working:

Sub KeepOnlyAtSymbolRows()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    Dim s As Integer

    s = Worksheets("Para").Range("C6")

    Set ws = ActiveWorkbook.Sheets("Report")

    lastRow = ws.Range("Y" & ws.Rows.Count).End(xlUp).Row

    Set rng = ws.Range("A1:AR" & lastRow)

    ' filter and delete all but header row
    With rng
        .AutoFilter Field:=1, Criteria1:="<>*s*"
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    ' turn off the filters
    ws.AutoFilterMode = False
End Sub

Shai Rado Shai Rado · Accepted Answer · 2017-03-28T11:45:18

Try the code below.

If your rng is from Column "A" to Column "AR", and you want to filter according to Column "Y", you need to Filter Field:=25 (not Field:=1 , which is Column A).

Also, to filter according to a value of a Variable, you need to take the variable name s outside the ", like Criteria1:="<>" & s.

Code

Option Explicit

Sub KeepOnlyAtSymbolRows()

Dim ws          As Worksheet
Dim rng         As Range
Dim lastRow     As Long
Dim s           As Variant

s = Worksheets("Para").Range("C6").value

Set ws = ActiveWorkbook.Sheets("Report")    
With ws
    lastRow = .Range("Y" & .Rows.Count).End(xlUp).Row
    Set rng = .Range("A1:AR" & lastRow)
End With

' filter and delete all but header row
With rng
    .AutoFilter Field:=25, Criteria1:="<>" & s, Operator:=xlAnd
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

' turn off the filters
ws.AutoFilterMode = False

End Sub

Delete all rows in a column that don't match a value in another worksheet using VBA

1 Answers