0

After filtering on a column I end up with 4 rows instead of 105 but my code is still seeing 105 rows, how can i automatically refresh my pivot table as i appy a filter.I'll filter my sheet many times so i want my pivot table to adapt to my filtering (don't want to apply the filter directly on the pivot table but to automatize the procedure)

Filtered items

LastRow = alarmes.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = 11
Set PRange = alarmes.Cells(1, 1).Resize(LastRow, LastCol)

Set PCache = Application.ThisWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PRange)
Set PTable = PCache.CreatePivotTable _
    (TableDestination:=graphe_dos.Cells(1, 1), TableName:="Test")
Ibrahim
  • 79
  • 10
  • 1
    I believe you're looking for the [Range.SpecialCells Method](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-specialcells-method-excel) with the `Type` argument `xlCellTypeVisible`. – user7393973 Aug 13 '18 at 13:38
  • i tried `LastRow = alarmes.Cells.SpecialCells(xlCellTypeVisible).End(xlUp).Row` but i'm getting only 1 line instead of 4 – Ibrahim Aug 13 '18 at 13:59
  • 1
    To provide some example code. You can see the difference between using `For Each Cell In ActiveSheet.ListObjects("Table1").ListColumns(1).DataBodyRange.SpecialCells(Type:=xlCellTypeVisible) Cell.Select Next` and the same but without the `SpecialCells` (while debugging, step by step so you see which cells are selected). You can't use it to get the `LastRow` as that would be 105 and then you would loop from row 2 to 105, you need to use it to get the range 2 to LastRow filtered which turns into 58;68;104;105 and then loop that range only in your code. – user7393973 Aug 13 '18 at 14:01
  • If you're still confused try just adding `SpecialCells` to the end of the line of code where you set `PRange `. – user7393973 Aug 13 '18 at 14:06
  • With `SpecialCells` after `PRange` the pivot table is empty – Ibrahim Aug 13 '18 at 14:09

1 Answers1

0

You can't create a PivotTable from a non-contiguous range. And filtering a PivotTable's source data has no effect on a PivotTable. So you either need to delete the hidden rows in the source Table, and then use the entire remaining Table block, or filter the PivotItems in the PivotTable itself.

If you want to filter a PivotField to match the items in the Table, then you need to iterate through the PivotItems (or SlicerItems if there is a Slicer connected to the PivotField) and set the .Visible (or .Selected for SlicerItems) property to TRUE for the things you want to keep, and FALSE for other items.

There are many potential bottlenecks when doing this, and if you don't code around them, then it might take minutes to iterate through a PivotField with as little as 20,000 PivotItems in it. See my post at http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/ for more on what to watch out for, and then see my answers at the following links that program around these bottlenecks:

Filtering on SlicerItems

Filtering on PivotItems

Perhaps an easy workaround is to set up a PivotTable Slicer, and use the value that the user selects in that PivotTable Slicer to filter the Table on the same value. See How to link a Table and a Pivot Table using Slicers in Excel?

jeffreyweir
  • 4,668
  • 1
  • 16
  • 27
  • thank you for help, but isn't there something much simplier like my prange is only the visible cells? something like this isn't possible? **`Set PRange = alarmes.Cells(1,1).Resize.SpecialCells(xlCellTypeVisible)`** or **`Set PRange = alarmes.UsedRange.SpecialCells(xlCellTypeVisible)`** – Ibrahim Aug 14 '18 at 06:26
  • Only if you then copy PRange somewhere else in a continuous block and *then* make a pivottable out of it. – jeffreyweir Aug 14 '18 at 06:43
  • Ok i see but i've already created multiple sheets to concatenate data, i have to create graphic too so i think it's better not to create another sheet just to copy data (workbook weight 20mb already).I've went to your **website link** and **Filtering on PivotItems** but i didn't fully understand it yet and how i can use it on my macro – Ibrahim Aug 14 '18 at 06:54
  • What version of Excel do you have? – jeffreyweir Aug 14 '18 at 08:16
  • I have the 2013 Excel – Ibrahim Aug 14 '18 at 09:16
  • Okay...i'm thinking of how something called the DataModel might be able to help. Hey...can you amend your question to give more context about what you're trying to achieve with this? i.e. why you want to have your PivotTables only include filtered data from the Table, as opposed to just filtering the PivotTable directly. Better to put it in your original question than here in the comments though, because it will help other people work out how best to advise you to proceed. – jeffreyweir Aug 14 '18 at 09:41
  • Can you also mark my answer as helpful if you've found it so? – jeffreyweir Aug 14 '18 at 09:42
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/178004/discussion-between-ibrahim-atto-and-jeffreyweir). – Ibrahim Aug 14 '18 at 11:16
  • i change the question, i don't know if it's much understandable. sorry i'm having hard time with english – Ibrahim Aug 14 '18 at 11:17
  • How is the Table data source being filtered? – jeffreyweir Aug 14 '18 at 18:46
  • @Ibrahim Did you resolve this in the end? Did my answer help? – jeffreyweir Sep 22 '18 at 23:18