delete all rows but rows with value =

[yoram]

hi,
i'm trying to modify the following code from Ron de Bruns Excel page.
i need the macro to delete all rows that do not equal "abc" in column
C; instead of deleting all rows with "abc" as it currently does. how
do i modify this? If i use "<>" by Criteria1 i get an error. also,
would it be possible to create a prompt for this 'do not delete' value?


Sub Delete_with_Autofilter()
Dim DeleteValue As String
Dim rng As Range

DeleteValue = "abc"

With ActiveSheet
.Range("A1:C40000").AutoFilter Field:=3, Criteria1:=DeleteValue
With ActiveSheet.AutoFilter.Range
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
.AutoFilterMode = False
End With
End Sub

 

[Die_Another_Day]

Sub Delete_with_Autofilter()
Dim DeleteValue As String
Dim rng As Range


DeleteValue = InputBox("Please Enter Data to Filter")


With ActiveSheet
.Range("A1:C40000").AutoFilter Field:=3, Criteria1:="<>" &
DeleteValue
With ActiveSheet.AutoFilter.Range
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
.AutoFilterMode = False
End With
End Sub

Give that a shot and let me know if it works.

Charles Chickering

 

[yoram]

That works great. Thanks a bunch!!