If range contans "0", mark as incomplete

[brent.fraser]

Hi all,

I know that this is simple (maybe not) but I have a report that has over 900,000 rows and I need to figure out what is complete and what is not.

I will use a for loop to loop through all of the rows and in each row, I have columns (H:AG) that will either contain a "1" or a "0." If any of these columns contain a "0" then that row item isn't complete.

Is there a way to check in it's entirety that H:AG contains or doesn't contain a "0"? I have seen how to do it on a cell by cell instance but not a "snapshot" of the entire range.

Still plugging away on it....

Thanks.

[mdmackillop]

Something like this will put Incomplete in column F

Sub Test()
    Dim r As Range
    Set r = Range(Cells(1, "F"), Cells(900000, "F"))
    With r
        .FormulaR1C1 = "=COUNTIF(RC[2]:RC[27],0)>0"
        .Value = .Value
        .Replace What:=False, Replacement:=""
        .Replace What:=True, Replacement:="Incomplete"
    End With
End Sub

[brent.fraser]

Thanks for the code MD...

Worked out very well. Now I am able to parse all 900,000 rows in a simple action.

Have a good one!

Brent