VBA Code to Count No of Rows with the same Entries in the Columns

[OilEconomist]

Thanks in advance for your assistance. What is the VBA Code to find the no of rows with the same specified values in the columns.
For example, I would like to find the number of rows with "Dog", "Cat" and "Bird, where in the following example, the answer is 3.

Book1
	A	B	C
1	Dog	Bird	Cat
2	Mule	Donkey	Goat
3	Dog	Bird	Mule
4	Donkey	Bird	Cat
5	Dog	Bird	Cat
6	Snapper	Catfish	Salmon
7	Dog	Bird	Cat
Sheet1

 

[Peter_SSs]

Could your data ever include the same cell value in multiple columns on the same row? That is, could you ever have these sorts of rows?

OilEconomist.xlsm
	A	B	C
10	Dog	Cat	Dog
11	Bird	Bird	Bird
Sheet1

 

[OilEconomist]

Could your data ever include the same cell value in multiple columns on the same row? That is, could you ever have these sorts of rows?

OilEconomist.xlsm
	A	B	C
10	Dog	Cat	Dog
11	Bird	Bird	Bird
Sheet1

As always thanks @Peter_SSs for your response and contributions to the forum.
For the data set I am working with, it does not vary per your example, but if you are able to resolve my initial request, I would also be interested in knowing the VBA code for the example you posted.

So the answer to your question is no.

 

[Peter_SSs]

So the answer to your question is no.

Thanks. The method that I was contemplating when I asked the question would not have worked with rows like I asked about. However I think the the method that I have ended up with below should work whether such rows exist or not.

Sub Count_Rows()
  Dim aWords As Variant, a As Variant, aWd As Variant
  
  Const myWords As String = "Dog|Cat|Bird"
  
  aWords = Split("|" & Join(Split(myWords, "|"), "|^|") & "|", "^")
  With Range("A1", Range("C" & Rows.Count).End(xlUp))
    a = Application.Transpose(Evaluate("""|""&" & .Columns(1).Address & "&""|@|""&" & .Columns(2).Address & "&""|@|""&" & .Columns(3).Address & "&""|"""))
    For Each aWd In aWords
      a = Filter(a, aWd)
    Next aWd
  End With
  MsgBox myWords & " rows = " & UBound(a) + 1
End Sub

 

[igold]

Thanks. The method that I was contemplating when I asked the question would not have worked with rows like I asked about. However I think the the method that I have ended up with below should work whether such rows exist or not.

Sub Count_Rows()
  Dim aWords As Variant, a As Variant, aWd As Variant
 
  Const myWords As String = "Dog|Cat|Bird"
 
  aWords = Split("|" & Join(Split(myWords, "|"), "|^|") & "|", "^")
  With Range("A1", Range("C" & Rows.Count).End(xlUp))
    a = Application.Transpose(Evaluate("""|""&" & .Columns(1).Address & "&""|@|""&" & .Columns(2).Address & "&""|@|""&" & .Columns(3).Address & "&""|"""))
    For Each aWd In aWords
      a = Filter(a, aWd)
    Next aWd
  End With
  MsgBox myWords & " rows = " & UBound(a) + 1
End Sub

@Peter_SSs, your code will fail with more than 65,536 rows of data.

Something like this, while being less sophisticated, runs 3x faster and does not fail (at least up to the 200k that I tested for)

Sub CountRows()

    Dim rw As Range, arr, i As Long, lrow As Long, ct As Long
    
    arr = Range("A1", Range("C" & Rows.Count).End(xlUp))
    For i = 1 To UBound(arr)
        Select Case arr(i, 1)
            Case Is = "Dog"
                If arr(i, 2) = "Bird" Then
                    If arr(i, 3) = "Cat" Then ct = ct + 1
                End If
        End Select
    Next
    MsgBox ct & " Occurences"
    
End Sub

 

[Peter_SSs]

@Peter_SSs, your code will fail with more than 65,536 rows of data.

Thanks. Yes I was aware of that. Happy to address that if required.

Note too that your code fails if the values are not in a particular order according to my reading of the question. Note the order of the items in the question and expected answer, compared to the sample data.

.. I would like to find the number of rows with "Dog", "Cat" and "Bird, where in the following example, the answer is 3.

Book1
	A	B	C
1	Dog	Bird	Cat
2	Mule	Donkey	Goat
3	Dog	Bird	Mule
4	Donkey	Bird	Cat
5	Dog	Bird	Cat
6	Snapper	Catfish	Salmon
7	Dog	Bird	Cat
Sheet1

 

[igold]

No, my code does not fail using the OP's data extended and as asked.