Delete rows

[Hans Knudsen]

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

 

[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

 

[Mike H]

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]

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