Automatic advanced filter when document opens

**dxdiag** · 02-21-2009, 01:05 PM

Hi, I have an advanced filter that works perfectly, but is there any way to have it use this filter automatically when the document opens, instead of having to go to DATA -> FILTER -> ADVANCED FILTER-> OK every time I open it? I know it s just a little extra effort but I'm trying to impress my boss, thanks!

**VBA Noob** · 02-21-2009, 01:20 PM

Place the code in ThisWorkbook module.

Record code the actions and change as required. e.g

Private Sub Workbook_Open()
 Sheets("Sheet2").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, Unique:=False
End Sub

VBA Noob

**mandi910** · 03-24-2009, 05:33 PM

I have an added layer to this question....

I currently have the above code in place to automatically apply an advanced filter when I click a form control button in my worksheet. However, I am running into some problems because I can only apply the code to the first row within my criteria range (i.e. it applies the AND criteria across the first row but not OR criteria down a column).

I have multiple qualifiers or criteria that each have a different number of options (ex: Criteria = Business Type, Options = B2B, B2C, or Hybrid, etc.) ---
What I think the code needs to do is determine which row within the criteria range has the last text entry and then filter from that row and above. I cannot simply select the entire range in the macro because any completely blank rows tell the filter to pull the entire data set.

Has anyone encountered this before?

**VBA Noob** · 03-24-2009, 05:35 PM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.