Efficient STRING search with SpecialCells
 

Hi, all
I need an efficient way to search for cells containing non-numeric
values in column A and then delete the entire row.
Since my worksheet is large (~60,000 rows) I need an inexpensive
method.
I tried using the selection: ".SpecialCells(xlCellTypeConstants,
xlTextValues)" but when I remove a row, all the following rows in the
collection change location and the code misses some of them.

Here is my code:
----------------------------------------
Rng = "a4:a60000"
Set MyRange = Range(Rng)
MyRange.Select
For Each MyCell In Selection.SpecialCells(xlCellTypeConstants,
xlTextValues)
MyCell.Activate
ActiveCell.Rows("1").EntireRow.Select
Selection.Delete Shift:=xlUp
Next MyCell
--------------------------------------

Any ideas?

Thanks
Ilan

Efficient STRING search with SpecialCells
 

Rng = "a4:a60000"
Set MyRange = Range(Rng)
MyRange.SpecialCells(xlCellTypeConstants, _
xlTextValues).EntireRow.Delete

This might have problems if there are more than 8192 areas to delete. If
so, post back.

--
Regards,
Tom Ogilvy


"Ilan" wrote in message
om...
Hi, all
I need an efficient way to search for cells containing non-numeric
values in column A and then delete the entire row.
Since my worksheet is large (~60,000 rows) I need an inexpensive
method.
I tried using the selection: ".SpecialCells(xlCellTypeConstants,
xlTextValues)" but when I remove a row, all the following rows in the
collection change location and the code misses some of them.

Here is my code:
----------------------------------------
Rng = "a4:a60000"
Set MyRange = Range(Rng)
MyRange.Select
For Each MyCell In Selection.SpecialCells(xlCellTypeConstants,
xlTextValues)
MyCell.Activate
ActiveCell.Rows("1").EntireRow.Select
Selection.Delete Shift:=xlUp
Next MyCell
--------------------------------------

Any ideas?

Thanks
Ilan