Control Pivot filter by From ComboBox

[Afarag]

Dears,

please i need help in filtering a range of date via combobox,
i have two comboboxs need one to start the range and the second select the last of range

eg: if i need to filter a range from 1- Jan till 18- Jan,
1st combobox select 1- Jan, 2nd select 18- Jan.

Thanks a lot,

 

[Hui]

A few issues here

Set your Pivot Table (PT) to a set range not the whole Columns
I'd suggest Source!$P$1:$U$436
In P2:U5 put 0's or formulas that return 0's if you can't have a value

The blanks are causing Excel to think that the Date filter is a Column of Text, not a column of Dates

Then use the following code:

Sub Filter()
'

Dim DateFrom As String
Dim DateTo As String

DateFrom = Application.WorksheetFunction.Text(Range("M2").Value, "dd/mmm/yyyy")
DateTo = Application.WorksheetFunction.Text(Range("N2").Value, "dd/mmm/yyyy")

ActiveSheet.PivotTables("PVT1").PivotFields("Date").ClearAllFilters

ActiveSheet.PivotTables("PVT1").PivotFields("Date").PivotFilters.Add _
  Type:=xlCaptionIsBetween, _
  Value1:=DateFrom, _
  Value2:=DateTo


End Sub

Sub Clearfilters()
  ActiveSheet.PivotTables("PVT1").PivotFields("Date").ClearAllFilters
End Sub

You will need to add two buttons to access the Subroutines

Also in M2 =INDEX($B$5:$B$50,M1)
N2: =INDEX($B$5:$B$50,N1)

Or see as per attached file:

 

[Afarag]

Hi Hui,

so sorry, but when selecting the date from both of 2 combobox then click to filter nothing happen

after click "filter"

is that something must taken in consideration

thanks a lot,

 

[Hui]

Did you enable macro's when the file loaded ?
Are you using Excel on a Mac ?

Change Source!R2 to =IF(ISBLANK(C2),0,C2)
Copy down to bottom of data

Goto Pivot
Refresh the PT
Clear all Filters

Replace the code in VBA with

Sub Filter()

Dim DateFrom As String
Dim DateTo As String

DateFrom = Application.WorksheetFunction.Text(Range("M2").Value, "dd/mmm/yyyy")
DateTo = Application.WorksheetFunction.Text(Range("N2").Value, "dd/mmm/yyyy")

ActiveSheet.PivotTables("PVT1").PivotFields("Date").ClearAllFilters

ActiveSheet.PivotTables("PVT1").PivotFields("Date").PivotFilters.Add _
  Type:=xlDateBetween, _
  Value1:=DateFrom, _
  Value2:=DateTo

End Sub

or see attached file

 

[Afarag]

Mr Hui,

thanks a lot, the last version do perfect job,

Gratefully,

 

[Hui]

The PT can't contain non-date values like spaces or blanks
0 is ok as that is a valid date