SpecialCells Value=0??
 

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

SpecialCells Value=0??
 

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

SpecialCells Value=0??
 

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