Thread: Sum of rows on an autofilter Thanks: 0 Likes: 0

1. Sum of rows on an autofilter

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  Reply With Quote

2. Re: Sum of rows on an autofilter

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```  Reply With Quote

3. Re: Sum of rows on an autofilter

[QUOTE=Michael M;5279209]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  Reply With Quote

4. Re: Sum of rows on an autofilter

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  Reply With Quote

5. Re: Sum of rows on an autofilter

Can you post a small sample of your data AND A1  Reply With Quote

6. Re: Sum of rows on an autofilter Originally Posted by Michael M 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  Reply With Quote

7. Re: Sum of rows on an autofilter

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```  Reply With Quote

8. Re: Sum of rows on an autofilter

Thankyou that works - instead of the msg box what adjust is need for the result to be in example M3  Reply With Quote

9. Re: Sum of rows on an autofilter

Would it be replace line msg box with

Range(“M2”).Value = n  Reply With Quote

10. Re: Sum of rows on an autofilter

Correct...well done...   Reply With Quote

User Tag List

cell, column, data, rows, text 