How to set up a class module in order to remove rows with zeros in them? 2

[feipezi]

Hi,

I know the statement .SpecialCells(xlCellTypeBlanks).EntireRow.Delete is quite useful as you try to remove entire rows with blank cells. But what if I need to remove rows with zeros? I know we don't have a valid statement like .SpecialCells(xlCellTypeZeros).EntireRow.Delete. Can we create one? What if I want to remove rows with a number like "999" or "777"?

Can we set up a Class module to make those statements valid? I played with Class module before but nothing serious.

Btw, I don't want to do something like the following:

Sub test()
For Each c In Selection
If c.Value = "999" Then c.EntireRow.Delete
Next
End Sub


If I have half-million records, it will take forever to get the job done.

Thanks in advance.

 

[SkipVought]

HI,

So .SpecialCells() requires a range on which to determine if the data in the range meets the specified criteria.

So if the criteria were 999, then any row in the range where the QUOTIENT of sum of all the cells in the range in a row and the COUNT of the same equals the criteria, then TRUE.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[feipezi]

Thanks Skip. But I don't quite understand. Sounds like no need for a Class module; sounds like it must be numeric. What if they are characters? Can I have a statement or simple example?

The data looks like this:

col A B C

999 2 5
3 9 0
999 4 7
6 10 8
....

The intention is to remove the first and the 3rd row.

Thanks again.

 

[SkipVought]

how about filter on 999 in column A?
SpecialCells xlTypeVisible, with the exception of the header row?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

You can first detect cells with 0s and next delete rows in single action:

[pre]Dim rngRef As Range, rngOut As Range, c As Range
Set rngRef = Selection
For Each c In rngRef
If c = 0 Then
If rngOut Is Nothing Then
Set rngOut = c
Else
Set rngOut = Range(rngOut, c)
End If
End If
Next c
rngOut.EntireRow.Delete[/pre]

combo

 

[feipezi]

Thanks guys!

But combo's way needs to sweep the whole file. Like what I said before: what if I have half-million records? It will take forever.

Here is the code based on what Skip suggested. It really works.

Thanks you both again.

Sub RemoveZeros()
Rows(1).AutoFilter Field:=2, Criteria1:="0", Operator:=xlFilterValues
Cells(1, 1).CurrentRegion.Offset(1).Select
With Selection
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
End Sub

Not only that, I can place an Array in the condition statement like this:

Criteria1:=Array("999", "888", "0")

This way, I can remove the records with 3 or more different values in a file (or range).