Results 1 to 5 of 5

COUNTA() in VBA

This is a discussion on COUNTA() in VBA within the Excel Questions forums, part of the Question Forums category; Hi, I would like to use counta to count the number of cells containing data in a specific row by ...

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    4

    Default

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

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



  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530

    Default

    What event are you using to trigger this code? Are you using the cells Change event?

    It looks to me like you are just running this code once, so if it's not being triggered by the Change event I recommend using it.

    If you need any further help, just repost.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303

    Default

    Try this

    sub countrange()

    ' if you have data in b1

    dim i as integer

    i = [b1].currentregion.rows


    [a1] = i

    end sub

    i hope this will help you..

    this is dyanmic.. go on adding data and you can get the counts of data.. if you have header then make minus -1

    ni****h
    http://www.pexcel.com

  4. #4
    MrExcel MVP Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,424

    Default

    If you want to use formula

    Sub test()
    Dim I As Long, J As Integer, K As Integer
    I = 1: J = 2: K = 8
    Cells(1, 1).Formula = "=countA(" & Range(Cells(I, J), Cells(I, K)).Address & ")"
    End Sub


    If you want to use Worksheet Change event...

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim I As Long, J As Integer, K As Integer
    I = 1: J = 2: K = 8
    If Target.Row <> I Then Exit Sub
    MsgBox Application.WorksheetFunction.CountA(Range(Cells(I, J), Cells(I, K)))
    End Sub

  5. #5
    New Member
    Join Date
    Apr 2011
    Posts
    30

    Default Re: COUNTA() in VBA

    hie,

    I almost have a similar question, I wanted to find out how do i do a:
    counta macro for multiple statements:
    e.g.
    Code:
    =COUNTA(TABF10!$F:$F)-2
    =COUNTA(TABF124!$G:$K)-2
    plus 10 there are tables like this with different lookup ranges and from different worksheets.

    Thanks

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
  •  


DMCA.com