Sum of rows on an autofilter

fireslguk

Active Member
Joined
Nov 11, 2005
Messages
279
Hi :)

I would like to add text in a cell e.g a1 and then run a macro which would search for a column of text data identifying if each cell contains the text in A1 and then gives the answer of number of rows
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,803
Office Version
2013
Platform
Windows
Are you searching for partial text in the cell, or just the value in the cell ??
Also, what does this have to do with Autofilter ??
If it's whole text use
Code:
Sub MM1()
Dim r As Integer
r = Application.WorksheetFunction.CountIf(Range("B2:B15"), Range("A1"))
MsgBox r
End Sub
 
Last edited:

fireslguk

Active Member
Joined
Nov 11, 2005
Messages
279
Are you searching for partial text in the cell, or just the value in the cell ??
Also, what does this have to do with Autofilter ??
If it's whole text use
Code:
Sub MM1()
Dim r As Integer
r = Application.WorksheetFunction.CountIf(Range("B2:B15"), Range("A1"))
MsgBox r
End Sub

The cells will contain more than A1

So the autofilter has the function contains however it sorts the rows but not give me a result of number of rows
 

fireslguk

Active Member
Joined
Nov 11, 2005
Messages
279
Would be good to see the formula look down the column that contains what is in each cell and if any contain the value in A1 a sum of these cells
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,803
Office Version
2013
Platform
Windows
Can you post a small sample of your data AND A1
 

fireslguk

Active Member
Joined
Nov 11, 2005
Messages
279
Can you post a small sample of your data AND A1
Sure

Column e:e

S mean, e frost, d wester, g Collin

F drew, d wester, b Jones

S mean, g create, f drew

R acheu, s kilso, d wester, k miles

P ingos, f drew, s kilso, e frost

All the way down


A1 can be changed however let’s say F drew

So answer would be 3
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,803
Office Version
2013
Platform
Windows
Maybe this

Code:
Option Compare Text
Sub MM1()
Dim lr As Long, r As Long, n As Integer
lr = Cells(Rows.Count, "E").End(xlUp).Row
n = 0
For r = lr To 1 Step -1
    If InStr(Range("E" & r).Value, Range("A1").Value) Then
        n = n + 1
    End If
Next r
MsgBox "There are " & n & " occurences in the list"
End Sub
 

fireslguk

Active Member
Joined
Nov 11, 2005
Messages
279
Thankyou that works - instead of the msg box what adjust is need for the result to be in example M3
 

Watch MrExcel Video

Forum statistics

Threads
1,096,415
Messages
5,450,282
Members
405,601
Latest member
API_newnoob

This Week's Hot Topics

Top