COUNTA()

[brucew]

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!!

[Dave Hawley]

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.