Hide rows in pivot table wehere the values are "empty" (VBA)

[dkoek]

Hi,

I want to hide the entire row in a pivot table in which the value(-s of that row is either zero, empty, or #NB.
In the beginning of the year there wuill be o1 value in the pivot table (month=1).
At the end of the year there will be 12 values in the pivot table (month=12).

I want to solve this using VBA but I don't know how.

Can someone help me with the VB code ?

 

[oldbrewer]

how many variables each month eg sales,costs,grossprofit,netprofit

 

[dkoek]

Only one measure (variable): "Absence"
Max 12 months, minimum 1 month.

 

[dkoek]

I tried this, but it hide much to many rows:

Private Sub Worksheet_Activate()

Dim HiddenRow&, RowRange As Range, RowRangeValue&

'*****************************
'< Set the 1st & last rows to be hidden >
Const FirstRow As Long = 12
Const LastRow As Long = 400

'< Set the columns that may contain data >
Const FirstCol As String = "D"
Const LastCol As String = "E"
'*****************************

ActiveWindow.DisplayZeros = False
Application.ScreenUpdating = False

For HiddenRow = FirstRow To LastRow

'(we're using columns D to E here)
Set RowRange = Range(FirstCol & HiddenRow & _
":" & LastCol & HiddenRow)

'sums the entries in cells in the RowRange
RowRangeValue = Application.Sum(RowRange.Value)

If RowRangeValue > 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If

Next HiddenRow

Application.ScreenUpdating = True

End Sub

 

[oldbrewer]

	month	NYabsence	CAabsence	total
	jan	25	41	66
	feb	33	43	76
	mar	34	45	79
	apr			0
	may			0
	jun			0
	jul			0
	aug			0
	sep			0
	oct			0
	nov			0
	dec			0
	Why must you have a pivot table ?

<colgroup><col span="2"><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[oldbrewer]

with this data I would plot a dynamic vertical bar chart so only 3 bars this month, next month the april bar would appear making 4 bars

 

[dkoek]

Because it is a pivot table already.
The measeure within the pivottable is calculated.
Sometimes the resault is #N/a.
You can hide the #N/a witthin the optionsbar of the pivottable.
The dimension however is still visable therefore I want to hide the complete row.

PS: There is no total column.

 

[oldbrewer]

ok, build a new table by copying elements of the pivot table, I often do this to make it look more professional - use eg if (or(t3="",t3="grand total"),"",T3)