Advanced Filter...

[Darin Kramer]

Howdie!!!

Heres the problem
3 Sheets:
a)Filters
b)FAQ
c) FAQ_Filtered

On Filters Sheet
A user inputs a value (s) in Cell c2, and or c3 (up to c6).
He then clicks a button which launches a macro.

The macro applys an advanced autofilter on another sheet (called FAQ),
using the value he has input in Cell c2 etc. It then copies and pastes
the result to FAQ_Filtered.

It works fine. HOWEVER, when you enter a value in Cell C2 that appears
IN EVERY LINE of the FAQ sheet, it finds everything (which is also fine)
However the line of VBA below that says "ActiveSheet.ShowAllData" gives
an error because all data is already being shown. I need to tell it to
only showalldata if alldata is not already being shown...?

Any ideas...?

(Sounds complicated, but maybe I just explained it badly_

Code is below


With Sheets("FAQ")
Cells.Select
Range("A1:E40").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Sheets("Filters").Range("c1:c6"), Unique:=False
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

Sheets("FAQ_filtered").Visible = True
Sheets("FAQ_filtered").Select
Range("A1").Select
ActiveSheet.Paste
Cells.EntireColumn.AutoFit
Cells.Select
Selection.RowHeight = 15
ActiveWindow.Zoom = 80

'now remove advanced filter
Sheets("FAQ").Select
ActiveSheet.ShowAllData

'now take user to filtered sheet
Sheets("FAQ_filtered").Select
Range("a1").Select


End With
Sheets("FAQ_filtered").Select

End Sub



*** Sent via Developersdex http://www.developersdex.com ***

 

[Nigel]

Try the following fix........

'now remove advanced filter
Sheets("FAQ").Select
If ActiveSheet.FilterMode then ActiveSheet.ShowAllData

 

[Darin Kramer]

Your the man!! Works great thanks.

Do you have any idea why the file size gets so huge when you doing these
advanced filters (I actually think its select visible cells and paste
that does it) The spreadsheet has two lines on it, but is already
1.4megs big...?

Thanks

D


*** Sent via Developersdex http://www.developersdex.com ***