Excel XP, Win XP
Helping an OP.
The OP has a long column (5,000 - 10,000 rows) of mathematical formulas. A
number of the values are "0" (numerical, not text).
The OP wants to delete all the rows that have "0" in that column.
I can loop through all the cells, from the bottom up, with something like:
If TheRng(c).Value = 0 Then 'delete the row
This will take a while because the column is long.
My question:
Is there any way I can use something like:
TheRng.SpecialCells(xlCellTypeConstants, xlNumbers = 0)
so that I can operate on that range in one operation instead of looping?
Thanks for your time. Otto

Sub DeleteRows()
Dim iLastRow As Long
Dim rng As Range

With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Columns("A:A").AutoFilter
.Range("A1:A" & iLastRow).AutoFilter Field:=1, _
Criteria1:="=0", _
Operator:=xlAnd
Set rng = .Range("A2:A" & iLastRow).SpecialCells(xlCellTypeVisible)
rng.EntireRow.Delete
.Columns("A:A").AutoFilter
End With

End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Otto Moehrbach" wrote in message
...
Excel XP, Win XP
Helping an OP.
The OP has a long column (5,000 - 10,000 rows) of mathematical formulas.
A number of the values are "0" (numerical, not text).
The OP wants to delete all the rows that have "0" in that column.
I can loop through all the cells, from the bottom up, with something like:
If TheRng(c).Value = 0 Then 'delete the row
This will take a while because the column is long.
My question:
Is there any way I can use something like:
TheRng.SpecialCells(xlCellTypeConstants, xlNumbers = 0)
so that I can operate on that range in one operation instead of looping?
Thanks for your time. Otto

Thanks Bob. That's better than looping. Otto
"Bob Phillips" wrote in message
...
Sub DeleteRows()
Dim iLastRow As Long
Dim rng As Range

With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Columns("A:A").AutoFilter
.Range("A1:A" & iLastRow).AutoFilter Field:=1, _
Criteria1:="=0", _
Operator:=xlAnd
Set rng = .Range("A2:A" & iLastRow).SpecialCells(xlCellTypeVisible)
rng.EntireRow.Delete
.Columns("A:A").AutoFilter
End With

End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Otto Moehrbach" wrote in message
...
Excel XP, Win XP
Helping an OP.
The OP has a long column (5,000 - 10,000 rows) of mathematical formulas.
A number of the values are "0" (numerical, not text).
The OP wants to delete all the rows that have "0" in that column.
I can loop through all the cells, from the bottom up, with something
like:
If TheRng(c).Value = 0 Then 'delete the row
This will take a while because the column is long.
My question:
Is there any way I can use something like:
TheRng.SpecialCells(xlCellTypeConstants, xlNumbers = 0)
so that I can operate on that range in one operation instead of looping?
Thanks for your time. Otto