LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
Sin Sin is offline
external usenet poster
 
Posts: 6
Default Advance Filter & If Code

I have set up a code to do the following:
When the dropdown control in the spreadsheet changed
1. it will clear range F5:G20,
2. if user select "(all)", then it will perform advance filter on column V
(unique value only) and paste it to range G5:g20. However, if user select
any other value, it will perform advance filter on colum U & V with critieria
range F1:F2 and paste it at F5:G20 (I want to keep the data set)
3. Sort the result in order

I'm not sure why, but when I run the code, it will not preform the advance
filter task and also, it'll clear my data set in column U & V, could someone
please check my code below and give me an advice on how to correct it?

Private Sub DropDown83_Change()

Dim WS1 As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range

Set WS1 = Sheets("Filter")
Set rng1 = WS1.Range("F5:G20")
Set rng2 = WS1.Range("L2")

With Application
ScreenUpdating = False
End With


With WS1

rng1.ClearContents

If rng2.Value = "(all)" Then
Set rng3 = WS1.Range("V:V")
rng3.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("G5"), _
Unique:=True
Else
Set rng4 = WS1.Range("U:V")
rng4.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("F1:F2"), _
CopyToRange:=rng4, _
Unique:=True
End If

rng1.Sort Key1:=rng1, _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom

End With

With Application
.ScreenUpdating = True

End With
End Sub

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Advance Filter JRey Excel Discussion (Misc queries) 2 November 14th 08 07:01 PM
Advance Filter RKS Excel Discussion (Misc queries) 0 March 27th 08 09:41 AM
Code to Advance filter a list in a shared workbook ram Excel Programming 2 January 10th 06 02:46 AM
Advance filter search does not filter an exact match cfiiland Excel Programming 1 June 10th 05 12:44 PM
about advance filter Max New Users to Excel 1 December 30th 04 04:59 AM


All times are GMT +1. The time now is 09:32 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"