Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count nullstrings in pivot table (databodyrange)
Hello
I have nullstrings in a pivot table. I can count them with this code and place the result in cell G1: Sub count_nulls() Dim c as Range Dim i As Integer Range("A3").Select ActiveCell.PivotTable.DataBodyRange.Select For Each c In Selection If c = 0 Or c = "" Or c = Null Then i = i + 1 Else End If Next c Range("G1").Value = i Range("G1").Select End Sub I now wish to specify in more detail what range within the pivot data table the counting should take place in. Certain columns and rows should not be included. How do I exclude certain cells och ranges? från the "Databodyrange" ? It has to be dynamic as the pivot table changes alot. (number of columns and rows differs from time to time) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count nullstrings in pivot table (databodyrange)
Let rng represent a reference to your DataBodyRange, then
set rng = Range("B15:Z200") ? intersect(rng(1).Range("A1:B1,F1:M1,O1,Y1").entire column,rng).Cells.Address $B$15:$C$200,$G$15:$N$200,$P$15:$P$200,$Z$15:$Z$20 0 so this just looks at columns B to C, G to N, P and Z. You could then loop through that. In the above, A1 is relative to the "anchor" range which is B15 in the above. So Range("B15").Range("A1") is B15 and Range("B15").Range("B2") would be C16 as an example. -- Regards, Tom Ogilvy "jonasmj" wrote in message ups.com... Hello I have nullstrings in a pivot table. I can count them with this code and place the result in cell G1: Sub count_nulls() Dim c as Range Dim i As Integer Range("A3").Select ActiveCell.PivotTable.DataBodyRange.Select For Each c In Selection If c = 0 Or c = "" Or c = Null Then i = i + 1 Else End If Next c Range("G1").Value = i Range("G1").Select End Sub I now wish to specify in more detail what range within the pivot data table the counting should take place in. Certain columns and rows should not be included. How do I exclude certain cells och ranges? från the "Databodyrange" ? It has to be dynamic as the pivot table changes alot. (number of columns and rows differs from time to time) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count(s) in Pivot Table | Excel Discussion (Misc queries) | |||
Pivot Table Count | Excel Discussion (Misc queries) | |||
Pivot Table Sum vs Count | Excel Discussion (Misc queries) | |||
Pivot Table - Count If | Excel Discussion (Misc queries) | |||
Pivot Table Count | Excel Discussion (Misc queries) |