Tweek an Autofilter Macro

[stapuff]

In the code below the Criteria1 needs to be changed to say any value not formatted as a date.

Any idea's would be greatfully appreciated.

Thanks,

Kurt

Dim Rng As Range
Set Rng = Range("A1:I" & Range("A5000").End(xlUp).Row)
Rng.AutoFilter Field:=8, Criteria1:=" "
Rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False

 

[Tom Urtis]

If I'm wrong, sorry, but are you saying you want to delete any row which has a date value in column H? If so, this would do that:

Sub Test1()
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Dim Rng As Range
Set Rng = Range("A1:I" & Range("A5000").End(xlUp).row)
Rng.AutoFilter _
Field:=8, Criteria1:=">=" & CDbl(DateSerial(1900, 1, 1)), _
Operator:=xlOr, _
Criteria2:="<=" & CDbl(DateSerial(2300, 12, 31))
On Error Resume Next
With Rng
.Offset(1, 7).Resize(.Rows.Count - 1, 1).SpecialCells(12).EntireRow.Delete
End With
Err.Clear
ActiveSheet.AutoFilterMode = False
Set Rng = Nothing
Application.ScreenUpdating = True
End Sub


Or are you saying you want to keep dates and delete anything in column H other than a date? Maybe this?

Sub Test2()
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Dim Rng As Range
Set Rng = Range("A1:I" & Range("A5000").End(xlUp).row)
Rng.AutoFilter Field:=8, Criteria1:=">=a*"
On Error Resume Next
With Rng
.Offset(1, 7).Resize(.Rows.Count - 1, 1).SpecialCells(12).EntireRow.Delete
End With
Err.Clear
ActiveSheet.AutoFilterMode = False
Set Rng = Nothing
Application.ScreenUpdating = True
End Sub

 

[stapuff]

Tom-

Thanks for the post. It was your second suggestion I was looking for. I wanted to keep the dates. It did not filter out the rows where H had no value. I will add a second criteria.

I have another filter question. Can autofilter handle a scenario like If the left most character in column A <> "Z" then delete row?

I am only assuming but the * in =">=a*" is a wild card?

Thanks for your help.

Kurt

 

[Tom Urtis]

Yes you are correct, the asterisks are a wild card.

If I understand your example question, this might be what you are after:

Criteria1:="<>Z*"

 

[stapuff]

Tom -

Outstanding!!!!

Thank you for the help and learning experiance.

Kurt