Results 1 to 10 of 10

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

  1. #1
    Board Regular
    Join Date
    Nov 2005
    Posts
    259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,334
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    2 Thread(s)

    Default 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
    Last edited by Michael M; May 18th, 2019 at 02:40 AM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    Board Regular
    Join Date
    Nov 2005
    Posts
    259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  4. #4
    Board Regular
    Join Date
    Nov 2005
    Posts
    259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  5. #5
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,334
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Sum of rows on an autofilter

    Can you post a small sample of your data AND A1
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  6. #6
    Board Regular
    Join Date
    Nov 2005
    Posts
    259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum of rows on an autofilter

    Quote Originally Posted by Michael M View Post
    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

  7. #7
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,334
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    2 Thread(s)

    Default 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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  8. #8
    Board Regular
    Join Date
    Nov 2005
    Posts
    259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  9. #9
    Board Regular
    Join Date
    Nov 2005
    Posts
    259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum of rows on an autofilter

    Would it be replace line msg box with

    Range(“M2”).Value = n

  10. #10
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,334
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Sum of rows on an autofilter

    Correct...well done...
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •