Excel VBA Filtering - When filter value is not there

Question

Dear Viewer,

This is related to VBA Excel.

I have the below codes to filter the value but when the filtered value is not there it will throw an error and need a code where if the filtered value is not there, it should select the blank so that we will come to know there is not data.

Could you please help me on this?

I want to filter the MSME in MSMED field if MSME is not there then it should be blank.

Dim Ptable1 As PivotTable

Set Ptable1 = Pcache.CreatePivotTable(tabledestination:=Psheet.Cells(1, 4), TableName:="MSMEPivottable")

'//insert row field

With ActiveSheet.PivotTables("MSMEPivottable").PivotFields("Le Wd")

.Orientation = xlRowField

.Position = 1

End With

'//insert the data into value field with count option

With ActiveSheet.PivotTables("MSMEPivottable").PivotFields("Finumber")

.Orientation = xlDataField

.Function = xlCount

End With

'//drag the data into filter option

With ActiveSheet.PivotTables("MSMEPivottable").PivotFields("MSMED")

.Orientation = xlPageField

End With

'//filter only with MSME

ActiveSheet.PivotTables("MSMEPivottable").PivotFields("MSMED").ClearAllFilters

ActiveSheet.PivotTables("MSMEPivottable").PivotFields("MSMED").CurrentPage = "MSME"

Answer 1

Hi,

To handle such a scenario where the filtered value is not present, you can use error handling in VBA to check if the value exists in the filter and select the blank value if it doesn't.

'//filter only with MSME
With ActiveSheet.PivotTables("MSMEPivottable").PivotFields("MSMED")
    .ClearAllFilters
    On Error Resume Next
    .CurrentPage = "MSME"
    If Err.Number <> 0 Then
        .CurrentPage = "(blank)" ' Select the blank value if MSME is not present
    End If
    On Error GoTo 0
End With

In the code above, On Error Resume Next is used to ignore any errors that occur when setting the filter to a value that doesn't exist. The Err.Number property is then checked, and if it's non-zero, indicating an error, the filter is set to the blank value "(blank)".

By using this error handling approach, if "MSME" is not present in the filter, it will be automatically changed to the blank value, allowing you to know that there is no data for "MSME" in the pivot table.

Best Regards.