AutoFilter code to delete rows

[Hans Knudsen]

I want to use Ron de Bruin's
http://www.rondebruin.nl/delete.htm
AutoFilter code to delete a lot of rows.
The criteria for rows to be deleted is:
=OR(NOT(ISNUMBER(B9));AND(ISNUMBER(B9);ISTEXT(C9));LEFT(D9;4)="Side")
from row 9 to row 30.000.

Any help on how to put this formula into Ron's below code:

Regards
Hans Knudsen


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

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Fill in the value that you want to delete
'Tip: use DeleteValue = "<>ron" to delete rows without ron
DeleteValue = "ron"

'Sheet with the data, you can also use Sheets("MySheet")
With ActiveSheet

'Firstly, remove the AutoFilter
.AutoFilterMode = False

'Apply the filter
.Range("A1:A" & .Rows.Count).AutoFilter Field:=1,
Criteria1:=DeleteValue

With .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

'Remove the AutoFilter
.AutoFilterMode = False
End With

With Application
.ScreenUpdating = True
.Calculation = calcmode
End With

End Sub

 

[Mike H]

Hans,

I think your goung to have be a bit more definitive about what the delete
criteria is. The worksheet formula you gave

=OR(NOT(ISNUMBER(B9));AND(ISNUMBER(B9);ISTEXT(C9));LEFT(D9;4)="Side")

evaluates like this which wouldn't leave may lines un-deleted,

All 3 cells Blank= True
Number in B9 text in C9= True
Number in B9, "Side" in D9=True
Number in B9 text in C9 text in D9= True
B9 blank, text in c9, text in D9= True
B9 & C9 blank text in d9= True
B9 blank , text in C9, d9 blank= True

Mike

 

[ShaneDevenshire]

Hi,

You are looking for all cells that are not number or they are numbers and
they are text. Can't be both numbers and text. Forget the formula and tell
us in words what you want to make for deletion.

 

[Hans Knudsen]

Mike
This extract I have is really the most awful thing I have ever seen. Will
try to explain in some more detail:

There are a lot of blank rows which I need to delete. I meant to find such
rows by: NOT(ISNUMBER(Bx))
Most of the rows I need to keep have a date in column B (dd-mm-yyyy), that
is a 5-digit number and also have numbers in column C and D.

Some rows however have a 4-digit number in column B and in the same row
there is a text in column C (and number in column D). I meant to find such
rows by : AND(ISNUMBER(Bx),ISTEXT(C)).

Finally there are rows with a date in column B (dd-mm-yyyy) and in the same
row there is (a time in column C) and a text in column D where the first 4
characters are "Side". I meant to find that by: LEFT(Dx;4)="Side"

I know the above is just an explanation of the formula and actually I feel
that this is not what you ask for.

For blank cells it is sufficient to check column B

I would gladly tell more if I just knew exactly what more information you
need.

Hans

 

[Hans Knudsen]

I have tried to tell in words what I want to delete in my reply to Mike H.

Yuo wrote:

"You are looking for all cells that are not number or they are numbers and
they are text. Can't be both numbers and text"

I am (among other things) loking for cells that have a number in column B
and text in column C, not for cells that at the same time have both a number
and a text in the same cell as you seem to assume.

Hans Knudsen