population of pivotFields visibleitemslist from cube

See more:

MS-Excel

macros

, +

Hi

i would like to ask about pivot report. The scenrio is, i have set filter column in pivot report, unfortunately the column contains a null value as well, and i need to work for cube to remove that null value, the question is that having that null value, in column does the VBA Script written in background crashes? i am facing the error,'Application Defined or object defined Error' code:1004, when i am trying to iterate pivotfields.visibleitemslist. and i am doubtfull that error is occuring having null field in column. Any suggestions please. my code is as follows

pType = Sheet1.Cells(25,"B")

If pType = "(Multiple Items)" Then
 Dim pt As PivotTable
 Dim payTypeField As PivotField
 Dim items As Variant
 Dim Hitems As Variant
 Dim str As String
 Dim ptables As PivotTable

 Set pt = Sheet1.PivotTables("PivotTable3")
 pType = ""
 Set payTypeField = pt.PivotFields("[DimPayment].[Payment-TypeName]")
 For Each items In payTypeField.VisibleItemsList

  pType = pType + Replace(Mid(CStr(items), InStr(CStr(items), "&") + 2), "]",",")
  Next items
  pType = Mid(pType, 1, Len(pType) - 1)
End If
pType = "'"& pType & "'"