vbscript to remove row where values are not X

[bmontoni]

Hi All,

I have an excel file that contains a table with headers. Is there a VBScript to remove all rows where the value of column A is NOT "X"

Thanks in advance!!

 

[johnnyL]

This assumes header row is row 1:

Sub DeleteRows()
'
    With ActiveSheet
        .AutoFilterMode = False                                 '   Remove filter
        .UsedRange.AutoFilter Field:=1, Criteria1:="<>x"        '   Filter column A for not = "x or X"
        .AutoFilter.Range.Offset(1).EntireRow.Delete            '   Skip header row 1 & Delete the rest
        .AutoFilterMode = False                                 '   Remove filter which leaves remaining rows
    End With
End Sub