COUNTA()

brucew

New Member
Joined
Mar 23, 2002
Messages
4
Hi,

I would like to use counta to count the number of cells containing data in a specific row by using VBA. I know to use lets say:

cells(1,1).formula = "=COUNTA(B1:H1)" to return the value, however, I would like to use variables to substitute for B1 and H1. I would like for the value to dynamically change as I change add or delete cells in the range. I have tried many different ways, but none of them work... I came close with:

dim I,J,K
I=1
J=2
K=8
cells(1,1)= "=COUNTA(range(cells(1,2),cells(1,8)))"

But it doesn't work.

Then I tried like this:

dim I,J,K
I=1
J=2
K=8

cells(1,1) = application.COUNTA(range(cells(I,J),cells(I,K)))

It returned value but it doesn't dynamically change as I input or delete data from cells. Thanks in advance for the help!!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Bruce, you now have 4 questions the same on the same page. Two of these have been kindly responded to by other users. They also asked you questions to which you have not bothered to answer.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
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