Autofilter code not working

[microwaveref]

Hi, I'm trying to modify a code that I got here in your forum to fit my file, but I kept on getting an error and can't figure out what to do

I wanted to use my combobox(active x control) as my criteria in my filter

My ComboBox1 is located in J7, and is also linked in cell J7.

The criteria to filter is in C9

The range to filter is from C9 to J9

And what I want is when "ALL" is selected in my ComboBox, the filter will be refreshed.

Thanks! I hope you can help me resolve my issue.


Sub Filter()
Dim fld
Application.ScreenUpdating = False

If UCase(Range("J7").Value) <> "ALL" Then
Range("C9:J9").AutoFilter Field:=2, Criteria1:=Sheets("SEARCH").ComboBox1.Value, visibledropdown:=False
With ActiveSheet.AutoFilter
For fld = 3 To 9
If Not .Filters(fld).On Then
Range("C9:J9").AutoFilter Field:=fld, visibledropdown:=False
End If
Next fld
End With
Else
IF
Sheets("SEARCH").ComboBox1.Value = "ALL"
Range("C9:J9").AutoFilter Field:=fld, visibledropdown:=False
Range("A1").Select
End if
End If


End Sub

[Aussiebear]

Just a quick couple of points for you to consider:

1. Where ever possible use Option Explicit at the start of your code. This will force you to declare all your variables.

You have Dim fld, which means something to you but perhaps not much to VBA.

2. Name your objects. Things like combobox1 may means something to you right now, but in 6 months time will you remember what it is, particularily when you may have a number of objects. Its difficult for others when looking at your code to assist you if they run into unnamed objects.

[microwaveref]

Actually, I used Option Explicit, I just missed copying it. I started my selection to the SUB part

I got a new code from this forum as well, and it actually worked, but I just missed one condition.

Option Explicit
Sub Filter()
Dim rng As Range
If UCase(Range("C9").Value) <> "ALL" Then
Range("P1") = Range("C9")
Range("P2") = Sheets("SEARCH").ComboBox1.Value

Set rng = Range("C9").CurrentRegion
rng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("P1:P2"), Unique:=False

Range("P1:P2").ClearContents
End If


End Sub



I just don't know how to modify it.


I was able to filter based on my criterias, but when I filter "ALL" it hides everything

How will I write the function:

If ComboBox1 = "ALL", then refresh filter?

Thanks!

[Bob Phillips]

Is this what you mean?



Sub Filter()
Dim rng As Range
Set rng = Range("C9").CurrentRegion
If UCase(Sheets("SEARCH").ComboBox1.Value) <> "ALL" Then
Range("P1") = Range("C9")
Range("P2") = Sheets("SEARCH").ComboBox1.Value

rng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("P1:P2"), Unique:=False

Range("P1:P2").ClearContents
Else
rng.AdvancedFilter Action:=xlFilterInPlace, Unique:=False
End If
End Sub