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

slivesay

Board Regular
Joined
Jan 4, 2019
Messages
64
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
That would suggest there is no data in column A.
 
Upvote 0
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.
 
Upvote 0
Glad you were able to sort it & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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