Excel VBA validation not working with blanks

[CRUTHERFORD]

Hi,

I have the below code that filters a pivot table based on a cell value but if the cell value is blank it errors out, not sure what I'm doing wrong as it seems simple - any help appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)


' Filters Pivots table on Market Name
If Target.Row = 2 And Target.Column = 6 Then
If Target.Value = "All Markets" Then
Sheets("Pivots").PivotTables("PivotTable1").PivotFields("MarketName").CurrentPage = "All"
Else
Sheets("Pivots").PivotTables("PivotTable1").PivotFields("MarketName").CurrentPage = Target.Value
End If
End If


' Filters Pivots table on Channel
If Target.Row = 2 And Target.Column = 18 Then
If Target.Value <> "All Channels" Then
Sheets("Pivots").PivotTables("PivotTable1").PivotFields("Channel").CurrentPage = Target.Value
Else
Sheets("Pivots").PivotTables("PivotTable1").PivotFields("Channel").CurrentPage = "All"
End If
End If


' Filters Pivots table on Year
If Target.Row = 2 And Target.Column = 30 Then
If Target.Value <> "All" Then
Sheets("Pivots").PivotTables("PivotTable1").PivotFields("Publish_Year").CurrentPage = Target.Value
Else
Sheets("Pivots").PivotTables("PivotTable1").PivotFields("Publish_Year").CurrentPage = "All"
End If
End If


' Filters Pivots table on Month
If Target.Row = 2 And Target.Column = 42 Then
If Target.Value <> "All" Then
Sheets("Pivots").PivotTables("PivotTable1").PivotFields("Publish_Month").CurrentPage = Target.Value
Else
Sheets("Pivots").PivotTables("PivotTable1").PivotFields("Publish_Month").CurrentPage = "All"
End If
End If




End Sub

Thanks

 

[Roger Govier]

Hi

You cannot select a Null for any of the pivot fields.

You need to add a further condition to each of your tests like

[COLOR=#333333]If Target.Row = 2 And Target.Column = 6 And Target <>"" Then
[/COLOR]