Efficient STRING search with SpecialCells

  • Thread starter Thread starter Ilan
  • Start date Start date
I

Ilan

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
 
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.
 
Back
Top