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
"Mike H" <(E-Mail Removed)> wrote in message
news:B0B515FE-8126-4723-BEC7-(E-Mail Removed)...
> 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
>
>
> "Hans Knudsen" wrote:
>
>> 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
>>
Similar Threads
