dynamic way of Filter and delete

[Hudson]

Hi all,

I was wondering if there is way we can make below code dynamic . ok basically below code insert a filter for any given range and delete if certain criteria matches . because of huge data in my sheet it is taking time to respond and at times it wont respond too. I would really be thankful if some one make it very dynamic and quick

below is the code for your reference .


Sub filter_and_delet_Repcode()
Dim LR As Long
LR = Range("F" & Rows.Count).End(xlUp).Row
Range("F1:j" & LR).AutoFilter Field:=1, Criteria1:=Array("998", "999", "Z71", "="), Operator:=xlFilterValues
Range("F2:j" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Range("F1:j" & LR).AutoFilter
ActiveSheet.AutoFilterMode = False
End Sub

[Paul_Hossler]

I've always found filtering to be slow with lots of data

This is a method that I use

I wasn't sure it the "=" was to catch formulas or if the cell actually contains a =, but you can easily adjust this if you decide to try it



Option Explicit

Sub Macro1()

With ActiveSheet.Columns("F:F")
.Replace What:="999", Replacement:=True, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="998", Replacement:=True, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="Z71", Replacement:=True, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="=", Replacement:=True, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

On Error Resume Next
.SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
On Error GoTo 0
End With

End Sub

[Hudson]

Hi Paul .. thank you so much for your time ... I know I am asking you for more .. can you take me through the code please .. next time I should be able to do it ... a notes on each line with short narration wold be fine ...

[Hudson]

Hi paul.. I tried it but it is slower than my way .. I am sorry , I mean to say even this is taking time .

can you advice please.

[Paul_Hossler]

I'm surprised that it takes so long

How data do you have?

Maybe you could post a workbook with just the Col F data to we can test?

[Hudson]

I am not exactly using that code as above . however , I am using it for different purpose .
below is what it is .


Sub Macro1()

With ActiveSheet.Columns("A:A")
.Replace What:="0", Replacement:=True, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
On Error Resume Next
.SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
On Error GoTo 0
End With

End Sub


attached is the sample file for you . if possible can you also help me with blanks "=".
thanks again .

[Paul_Hossler]

.SpecialCells(xlCellTypeBlanks).EntireRow.Delete