vba pivot filter

[nick_138]

hi,

i'm trying to use a cell reference to filter a pivot field in vba. it's almost working...
this is the code i'm using

ActiveSheet.PivotTables("PivotTable1").PivotFields("Trans Week"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Trans Week").PivotFilters. _
Add Type:=xlCaptionIsBetween, Value1:=CStr(Range("E3").Value), Value2:=CStr(Range("E2").Value)

currently cell E2 is "42" and E3 "38" so the code should be showing me column 38, 39, 40, 41 however when it runs it throws in column 4 too... cannot figure out why.. if i set the values in the code to 38 and 42, fine, but using the cell ref... i don't know

any help would be much appreciated.

Thanks!

 

[pbornemeier]

The labels are seen as text and when sorting text values 4 is between 38 and 44.

 

[nick_138]

thanks! makes sense i suppose. any idea how to not include the 4?

 

[pbornemeier]

Can you convert that column to text and use leading zeros?
Or add a helper column with the same values and leading zeros and filter on that

 

[nick_138]

thanks pbornemeier

leading zeros and text now shows 038,039,04,040,041.

do you mean filter the helper column before creating the pivot?

why, when filtered through the pivot itself or specifying the number in the code, it does not include the 4? surely it would still be included if it was reading labels as text that way too? so how does that filter go numerically?

 

[pbornemeier]

in your list in Post #5, 04 should be 004.

Add enough leading zeros to items in that column so they are all as long as the longest.
If the largest number is 723 then 1 should be 001 and 44 should be 044.

I meant to format as text and add the zeros (or the helper column) in the source data for the PivotTable.

I am not sure why it is working that way it does, but treating them as text seems to work.