Sum of rows on an autofilter

fireslguk

Active Member
Joined
Nov 11, 2005
Messages
294
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Can you post a small sample of your data AND A1
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Thankyou that works - instead of the msg box what adjust is need for the result to be in example M3
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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