VBA Filter PowerPivot Field, based on range cell values but skipping Errors.

countryfan_nt

Well-known Member
Joined
May 19, 2004
Messages
688
Hello Friends, Hope all is safe & Well!

The following code, filters the PowerPivot's field PatientID, based on the Range B2:B1000 of sheet DBs.

The code works provided that whatever is in B exists in the PowerPivot field, If it doesn't I get an error.
How can I have the code run anyway & skip the cells in B that don’t exist in the field.

End result, filter only whatever is in B and matches what is in the field.

Your kind support is and always appreciated please.

Code:
Option Explicit

Sub Test1()

Dim MyString As String
Dim ArrVisiblelist()
Dim fldName As String
fldName = "[Proc XLSM].[PatientID].[PatientID]"
Dim lr As Long, i As Long
lr = Sheets("DBs").Range("B" & Rows.Count).End(xlUp).Row

ReDim ArrVisiblelist(1 To lr - 1)

For i = 2 To lr

ArrVisiblelist(i - 1) = "[Proc XLSM].[PatientID].&[" & Sheets("DBs").Cells(i, 2).Value & "]"

Next I

ActiveSheet.PivotTables("PT").PivotFields(fldName).VisibleItemsList = ArrVisiblelist

End Sub
 

Attachments

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Watch MrExcel Video

Forum statistics

Threads
1,102,563
Messages
5,487,583
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top