-
Dec 10th, 2013, 06:27 AM
#1
Thread Starter
Lively Member
[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
-
Dec 10th, 2013, 07:34 AM
#2
Addicted Member
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.
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.
-
Dec 10th, 2013, 07:40 AM
#3
Thread Starter
Lively Member
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.
-
Dec 10th, 2013, 08:44 AM
#4
Thread Starter
Lively Member
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))
-
Dec 10th, 2013, 03:39 PM
#5
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|