I have a sheet with (extract from ERP system) with about 30,000 rows. I need
to delete a lot of them (once a week). Tried the following formula:
=IF(AND(ISNUMBER(C2412),NOT(ISTEXT(B2412))),"","x" )
copied to for example AA1:AA30.000 to get 'x' in rows to be deleted.
Then I use AutoFilter to find all 'x' and try to delete all these rows, but
my Excel to freezes.

Next I wanted to use Ron de Bruin's code
http://www.rondebruin.nl/delete.htm

but I am not sure how to change the code to have it delete rows which both
have a number in column C and where column B is not text. Am I right that
Ron's code is optimized for speed?

Hans Knudsen

One more thing:
What should the code look like if I want to delete all rows which not hold a
seven digit number in column C?
Hans


"Hans Knudsen" wrote in message
...
I have a sheet with (extract from ERP system) with about 30,000 rows. I
need to delete a lot of them (once a week). Tried the following formula:
=IF(AND(ISNUMBER(C2412),NOT(ISTEXT(B2412))),"","x" )
copied to for example AA1:AA30.000 to get 'x' in rows to be deleted.
Then I use AutoFilter to find all 'x' and try to delete all these rows,
but my Excel to freezes.

Next I wanted to use Ron de Bruin's code
http://www.rondebruin.nl/delete.htm

but I am not sure how to change the code to have it delete rows which both
have a number in column C and where column B is not text. Am I right that
Ron's code is optimized for speed?

Hans Knudsen

To me nothing seems to happen when I run this procedure, and I have not
changed anything neither in my sheet nor in your code.
Hans


"Mike H" wrote in message
...
Hi,

I'd use a macro for this. Right click the sheet tab, view code and paste
this in and run it.

Sub Stantial()
Dim MyRange As Range
Dim copyrange As Range
Lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set MyRange = Range("B1:B" & Lastrow)
For Each c In MyRange
If Not WorksheetFunction.IsText(c.Value) And Not IsEmpty(c.Offset(, 1))
And
IsNumeric(c.Offset(, 1)) Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.EntireRow.Delete
End If

End Sub

Mike

"Hans Knudsen" wrote:

One more thing:
What should the code look like if I want to delete all rows which not
hold a
seven digit number in column C?
Hans


"Hans Knudsen" wrote in message
...
I have a sheet with (extract from ERP system) with about 30,000 rows. I
need to delete a lot of them (once a week). Tried the following formula:
=IF(AND(ISNUMBER(C2412),NOT(ISTEXT(B2412))),"","x" )
copied to for example AA1:AA30.000 to get 'x' in rows to be deleted.
Then I use AutoFilter to find all 'x' and try to delete all these rows,
but my Excel to freezes.

Next I wanted to use Ron de Bruin's code
http://www.rondebruin.nl/delete.htm

but I am not sure how to change the code to have it delete rows which
both
have a number in column C and where column B is not text. Am I right
that
Ron's code is optimized for speed?

Hans Knudsen