Retaining Rows based on Array Value
Below codes was a modified version that i got here to delete rows
based on the ColVal array...however need to revised this that instead
of deleting/removing rows based on the ColVal values, they should be
the one retained not removed. How can I do that?? Any help is
appreciated...
ColVal = Array( _
"VAL1", "VAL2", "VAL3", _
)
DeleteRow_Autofilter ColVal, LastRow
---
Sub DeleteRow_Autofilter(ColVal As Variant, LastRow As Long)
Dim Rng As Range
Dim I As Long
For I = LBound(ColVal) To UBound(ColVal)
ActiveSheet.Range("E1:E" & LastRow).AutoFilter Field:=1,
Criteria1:=ColVal(I)
With ActiveSheet.AutoFilter.Range
Set Rng = Nothing
On Error Resume Next
Set Rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not Rng Is Nothing Then Rng.EntireRow.Delete
End With
Next I
ActiveSheet.AutoFilterMode = False
End Sub
|