Pivot table Report filtering based on range of values

subrahmanyam

New Member
Joined
Feb 21, 2015
Messages
14
I had a pivot table with Report filter which has 8k items trying to filter and it is taking he'll lot of time if I give the last item of the pivot table in the range.

Could someone help me on this.

Thanks
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,188
  • Where is the normalized data table that is the source for your pivot table?
  • Filtering a huge pivot is expected to be slow. You may consider the following workaround:


  • Run a SQL query to filter the source table with the desired criteria.
  • Create the pivot based on the filtered data. It should give you the same result, but much faster.
  • VBA can be used to automate this process. I will prepare some sample code.
 

subrahmanyam

New Member
Joined
Feb 21, 2015
Messages
14
Hi Worf,

Thanks for responding.

My answers to your query

1.The source of the pivot is excel it self.

Thanks for the workaround.

Could you send me the sample files so that I will have a look.


Thanks a lot in advance.

Regards,
Subrahmanyam
 
Last edited:

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,188
Can you adapt this example to your specific configuration or would you like help with this?

Kk00J67.jpg


Code:
Sub rahmanyam()
RunSEL          ' filter raw data
PTable          ' create pivot table
End Sub


Sub RunSEL()
Dim cn As Object, rs As Object, i%
Set cn = CreateObject("ADODB.Connection")
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & _
    ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    .Open
End With
Set rs = cn.Execute("SELECT * FROM [sheet3$] WHERE Amount > 200")
i = 2
Do
    Cells(i, 8) = rs(0)
    Cells(i, 9) = rs(1)
    Cells(i, 10) = rs(2)
    Cells(i, 11) = rs(3)
    i = i + 1
    rs.Movenext
Loop Until rs.EOF
rs.Close
cn.Close
Set cn = Nothing
Set rs = Nothing
End Sub


Sub PTable()
Dim lr, sh As Worksheet, sdata$, dest$, pt As PivotTable
Set sh = Worksheets("sheet3")
lr = sh.Range("h" & Rows.Count).End(xlUp).Row
sdata = sh.Name & "!" & sh.[h1].CurrentRegion.Address(ReferenceStyle:=xlR1C1)
dest = sh.Name & "!" & sh.Cells(lr + 10, 8).Address(ReferenceStyle:=xlR1C1)
ActiveWorkbook.PivotCaches.Create(xlDatabase, sdata, 5).CreatePivotTable _
dest, "MyPivot", , xlPivotTableVersion15
Set pt = sh.PivotTables("MyPivot")
With pt.PivotFields("Region")
    .Orientation = xlPageField
    .Position = 1
End With
With pt.PivotFields("Employee")
    .Orientation = xlRowField
    .Position = 1
End With
pt.AddDataField pt.PivotFields("Amount"), "Sum", xlSum
pt.TableStyle2 = "PivotStyleMedium5"
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,826
Messages
5,855,863
Members
431,771
Latest member
CoryMelth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top