Macro to Add current selection to filter

[Analyze_This]

Hey guys,

I'm accessing a data cube and filtering one field (it has thousands) down to only a few hundred items. There is a cool feature in 2010 to search for items and a checkbox that says "Add current selection to filter"

I tried recording a macro to see what the code for this is, but it's the same as just checking the selections you want. So.....

1) does anyone know if there is code to add a selection to the filter or
2) does anyone know a way to filter a couple hundred items at once?

I tried just creating a very long code string, but VBA said it was too long to execute. Any thoughts?

 

[n0gel]

I've used this with success with a single criteria, so it may work for a range like say A10:A50...

Dim fCol As Long 'column to filter on sheet1
Dim Crit As String 'value to filter by
Dim LR As Long 'last row of visible filtered data
On Error Resume Next

fCol = 1 '1st column filtered is 1, 2nd column is 2, etc
Crit = Range("A10").Value 'uses value in cell Range for the filter
Application.CutCopyMode = False

With Sheets("Sheet1")
.Unprotect
.AutoFilterMode = False
.Range("B1:U1").AutoFilter Field:=fCol, Criteria1:=Crit

 

[Jerry Sullivan]

I'm accessing a data cube and filtering one field (it has thousands) down to only a few hundred items. There is a cool feature in 2010 to search for items and a checkbox that says "Add current selection to filter"

Do you have a filtered PivotTable with a data cube source?

If so, this thread might get you started toward a macro that could do what you describe.

http://www.mrexcel.com/forum/showthread.php?t=565273
You can jump to post #6 for the code.

 

[Analyze_This]

That is awesome, Jerry. Thanks!