Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filter...
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filter...
Try the following fix........
'now remove advanced filter Sheets("FAQ").Select If ActiveSheet.FilterMode then ActiveSheet.ShowAllData -- Cheers Nigel "Darin Kramer" wrote in message ... 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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filter...
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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Filter - filter rows < | Excel Discussion (Misc queries) | |||
Why won't advanced filter return filter results? | Excel Worksheet Functions | |||
How do I use advanced filter to filter for blank cells? | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) |