Results 1 to 7 of 7

Thread: Excel VBA Count Total Cells That Contain Value in a Column & Post Total in Cell After Last Cell

  1. #1
    New Member
    Join Date
    Jan 2019
    Location
    US
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Excel VBA Count Total Cells That Contain Value in a Column & Post Total in Cell After Last Cell

    Hello. Happy Friday!

    I have a sheet that I need to loop through all cells in a column and count the total number of cells that contain data (will always be text in cells) & put the total number in the cell after the last cell that contains data. The cells in question are in column A and my sheet does contain a header that does not need to be included in the count. My sheet contains data from columns A to Z.

    Can the below vba be changed some to accomplish this? The coding here sums up all the numbers in cells and puts the answer in the cell after the last row w/ values (text and numbers). This code was provide to me via a mrexcel member (Fluff) and it works wonderfully. IDK if it can be altered to accomplish what I need the new code to do or if different vba coding would be the best way to go.

    Code I'm currently using to sum up all numbers in a column & pasting answer in cell after last row that contains value via a command button:

    With Range("Y" & Rows.Count).End(xlUp).Offset(1, -14)
    .FormulaR1C1 = "=sum(r2c:r[-1]c)"
    .HorizontalAlignment = xlCenter
    End With



    I truly appreciate any assistance. Thank you so much!

    Respectfully,
    slivesay

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Excel VBA Count Total Cells That Contain Value in a Column & Post Total in Cell After Last Cell

    Maybe
    Code:
    Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 26).FormulaR1C1 = "=counta(r2c:r[-1]c)"
    This assumes that no column will be longer than col A
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Jan 2019
    Location
    US
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Count Total Cells That Contain Value in a Column & Post Total in Cell After Last Cell

    Hello Fluff! Hope all is well in your world.

    Thank you! It is posting the total at the top between the header row and the first data row. I'd like it to be placed at the bottom just like the coding you helped me w/ where a column that has numbers is added and the answer is at the bottom after the last row w/ data. It, also, horizontally centered the total. I only need the total of cell data for column A (not all columns between A-Y). Is this possible?

  4. #4
    New Member
    Join Date
    Jan 2019
    Location
    US
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Count Total Cells That Contain Value in a Column & Post Total in Cell After Last Cell

    Fluff, I changed your code to the below and it only does column A, but it's putting all my totals at the top right after the header. Any suggestions?

    With Range("A" & Rows.Count).End(xlUp).Offset(1)
    .FormulaR1C1 = "=counta(r2c:r[-1]c)"
    .HorizontalAlignment = xlCenter
    End With
    Last edited by slivesay; Sep 6th, 2019 at 02:42 PM.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Excel VBA Count Total Cells That Contain Value in a Column & Post Total in Cell After Last Cell

    That would suggest there is no data in column A.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Jan 2019
    Location
    US
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Count Total Cells That Contain Value in a Column & Post Total in Cell After Last Cell

    I changed it to Column B that has names in it. Column A held numbers instead of text (I misspoke about what the cells in col A held) - maybe that is why it was doing that. I did try to move the answer to the left one space to have it placed in column A, but it moved the totals to the top again. It works correctly if it is based on data in col B and pastes the answer in the cell after the last cell in column B.

    Thank you!! I hope you have a wonderful weekend.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Excel VBA Count Total Cells That Contain Value in a Column & Post Total in Cell After Last Cell

    Glad you were able to sort it & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •