# VBA to find rows with specific text and count the zeros

#### rbone

##### New Member
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### =ODIN=

##### Active Member
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:

#### rbone

##### New Member
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
.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
"D1:D9997"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Hour (2)").Sort
.SetRange Range("A1:IV9997")
.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.

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:

#### rbone

##### New Member
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.

#### SupremeDr

##### New Member
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``````

#### rbone

##### New Member
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"``

#### Joe4

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:

#### rbone

##### New Member
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.

#### rbone

##### New Member
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.

1,102,778
Messages
5,488,804
Members
407,658
Latest member
Arias610

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...