VBA to find rows with specific text and count the zeros

rbone

New Member
Joined
Aug 19, 2019
Messages
20
I'm looking for a macro to find multiple "Sub-Total" in Column D and count the number of "0"s in the corresponding rows.

There are many "0"s in the report, but I only want the "0"s in the Sub-Total rows.

Any assistance is greatly appreciated
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Does it need to be a macro?

You could use an array formula to do this in a helper column.


If your data has sub-total in column d, and you wanted to counted the zeros in columns e-h you could use this formula (assumes you enter it onto row 1 and then drag it down as far as needed).
Code:
=IF(D1="sub-total",SUM(--(E1:H1=0)),"Not A Sub Total Row")

However, it must be entered as an array formula. To do that, you would click into cell D1, paste the formula in, and hit Ctrl+Shift+Enter. You will know it worked correctly if you single click back on cell D1 and see the formula looking like this (notice curly brackets on ends)
Code:
{=IF(D1="sub-total",SUM(--(E1:H1=0)),"Not A Sub Total Row")}

It's worth noting that this method counts any blank cells as zero.
 
Last edited:
Upvote 0
Yes, it does need to be in a macro. It's part of a much larger macro to pull numbers for reports. The code below is just a single, small macro for the entire project.

The last step of this macro would be to count the zeros in the rows that are Sub-Total Rows.

At the end of this macro, I would need it to look at sheet "Hour", count the zeros in the Sub-Total Rows and put the count in C2 on sheet "Hour (2)"

Code:
Sub SFDHourCopyFormating28()

    Sheets("Hour").Select
    Sheets("Hour").Copy After:=Sheets(12)
    Sheets("Hour (2)").Select
    Cells.Select
    If ActiveSheet.AutoFilterMode Then
        ActiveSheet.AutoFilterMode = False
    End If
    With Selection
        .VerticalAlignment = xlTop
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Selection.RowHeight = 12
    Range("D35").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("D36").Select
    Selection.Cut
    Range("D37").Select
    ActiveSheet.Paste
    Cells.Select
    ActiveWorkbook.Worksheets("Hour (2)").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Hour (2)").Sort.SortFields.Add2 Key:=Range( _
        "D1:D9997"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Hour (2)").Sort
        .SetRange Range("A1:IV9997")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(R86C8:R113C58,"">90"")"
    Range("C2").Select
'   Here is where I need the new code to count the zeros
End Sub

Here's an example of the data seen, and the reason to count the zeros in the Sub-total Rows only.
open


The image isn't showing for me, in case it isn't for you, here's the link. https://drive.google.com/open?id=1oPBPiOKcZVHvVPpA-SckYWE2xAENuOD8

<tbody>
</tbody>
 
Last edited:
Upvote 0
Range("C2").Value = "=COUNTIF(addrangehre,0)"

Thank you, but that will count all zeros in a specific range. I can only use the zeros in the sub-total rows. There is 1 sub-total row for each date, and four other rows with many zeros for each date that I can not use. I'm hoping to avoid a separate =COUNTIF formula for each date and have the macro search for the sub-total rows and count the zeros.
 
Upvote 0
Thank you, but that will count all zeros in a specific range. I can only use the zeros in the sub-total rows. There is 1 sub-total row for each date, and four other rows with many zeros for each date that I can not use. I'm hoping to avoid a separate =COUNTIF formula for each date and have the macro search for the sub-total rows and count the zeros.


Sounds like you need to run a loop through each line with an if statement.

Code:
Dim wsHour2 As Excel.Worksheet
Set wsHour2 = Sheets("Hour 2")
Dim rngTable1 As Excel.Range
Set rngTable1 = wsHour2.Range("A1").CurrentRegion 'change A1 to the first cell in the table
rngRows = rngTable1.Rows.Count
rngRow1 = rngTable1.Rows(1).Count
Dim i As Long
[COLOR=#0000FF]Dim[/COLOR] iVal [COLOR=#0000FF]As Integer
Dim iTotal as Integer
iTotal = 0

For i = rngRow1 To rngRows
If cells (i, 4) = "Sub-Total"
[/COLOR]
iVal = Application.WorksheetFunction.COUNTIF(Range([COLOR=#CC0000]"A1:A10"[/COLOR]),[COLOR=#CC0000]"0"[/COLOR])
iTotal = iTotal + iVal
end if
next

Range("C2").Value = iTotal

end sub
 
Upvote 0
Sounds like you need to run a loop through each line with an if statement.

Code:
Dim wsHour2 As Excel.Worksheet
Set wsHour2 = Sheets("Hour 2")
Dim rngTable1 As Excel.Range
Set rngTable1 = wsHour2.Range("A1").CurrentRegion 'change A1 to the first cell in the table
rngRows = rngTable1.Rows.Count
rngRow1 = rngTable1.Rows(1).Count
Dim i As Long
[COLOR=#0000FF]Dim[/COLOR] iVal [COLOR=#0000FF]As Integer
Dim iTotal as Integer
iTotal = 0

For i = rngRow1 To rngRows
If cells (i, 4) = "Sub-Total"
[/COLOR]
iVal = Application.WorksheetFunction.COUNTIF(Range([COLOR=#CC0000]"A1:A10"[/COLOR]),[COLOR=#CC0000]"0"[/COLOR])
iTotal = iTotal + iVal
end if
next

Range("C2").Value = iTotal

end sub

Thanks for this!!! I'm getting a syntax error on

Code:
If cells (i, 4) = "Sub-Total"
 
Upvote 0
Thanks for this!!! I'm getting a syntax error on

Code:
If cells (i, 4) = "Sub-Total"
There are a few errors with that line of code. There should not be a space after "cells", and it is missing a "Then".
Try:
Code:
If Cells(i, 4) = "Sub-Total" Then
 
Last edited:
Upvote 0
There are a few errors with that line of code. There should not be a space after "cells", and it is missing a "Then".
Try:
Code:
If Cells(i, 4) = "Sub-Total" Then

Thank you! Getting closer!
If I start it at D48, it finds 0, if I start it at D340 it finds 1624. There should only be 3 zeros in the rows that contain "Sub-Total" in Column D in the entire report. Row 340 has the first 0 in a row that has "Sub-Total" in Column D, but the first Sub-Total row is Row 48.

I hope I'm making sense.
 
Upvote 0
I hope this clarifies what I'm looking for. I'm hitting a huge brick wall.
I run monthly reports, so each month the number of days is different so I can't hard code a range. I also have 13 locations and each template is slightly different so I need the code to be somewhat dynamic.

If column D is "Sub-Total"
Then Select Row (Row range is H to BE)
Count number of zeros in row
Loop until complete
Output total count to Cell C2


It seems it would be more simple to just have a macro copy the found rows to a new sheet and do a =COUNTIF for the zeros? I'm trying to avoid this.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top