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)

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)