Results 1 to 5 of 5

Thread: [RESOLVED] count non blank cells in data area within table object - result isn't what I expected

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jun 2012
    Posts
    90

    Resolved [RESOLVED] count non blank cells in data area within table object - result isn't what I expected

    Hi

    I have a 2 row table object named 'Header' that fills range A6 to L7. Row 6 is the header and row 7 has the data. I want to count the number of non blank cells in row 7 but I only get the value 1 returned when I expect 11 (I have removed one cell value from row 7 for the purpose of testing). The code I'm using is below.

    In the 'immediate' window I see that .Address is showing $A$6:$L$7 so I was feeling quote positive.

    Code:
    ?application.worksheetfunction.CountA(listobjects("Header").DataBodyRange.Address)
    Any idea where I'm going wrong?

    Thanks

  2. #2
    Addicted Member 3com's Avatar
    Join Date
    Jul 2013
    Location
    Spain
    Posts
    253

    Re: count non blank cells in data area within table object - result isn't what I expe

    You have two ways to get it.
    1 - via excel formula, look at the image below.

    Name:  CountNonEnptyCells.png
Views: 555
Size:  6.7 KB


    2 - via vba code.

    Code:
    Sub countNonEnptyCells()
        Sheets(1).Range("a4").Value = Application.WorksheetFunction.CountA(Range("a2:L2"))
    End Sub
    Note: Spanish Excel here. English Formula: =CountA(A2:L2)

    HTH
    Last edited by 3com; Dec 10th, 2013 at 07:37 AM.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Jun 2012
    Posts
    90

    Re: count non blank cells in data area within table object - result isn't what I expe

    Sure. thanks for that.

    I just want to make sure I'm not missing any "table" related function / feature that gives the same result. I mean, most what is in the COUNTA is covered using table related properties (listobjects("Header").DataBodyRange.Address). It's just the result that I get isn't what I expect.

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Jun 2012
    Posts
    90

    Re: count non blank cells in data area within table object - result isn't what I expe

    fixed it using the following;
    Code:
    Application.WorksheetFunction.CountA(Range(ListObjects("Header").DataBodyRange.Address))

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [RESOLVED] count non blank cells in data area within table object - result isn't

    Code:
    Application.WorksheetFunction.CountA(range("header"))
    should also work
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width