Using value from ComboBox in AdvancedFilter VBA code

Skippy

Board Regular
Joined
Mar 3, 2002
Messages
194
I'm struggling with the advanced filter using VBA.

In my case, the user selects an item from a ComboBox (e.g. "Apple"). Then, from a worksheet, I want to copy all unique "Brands" from Col G that match "Apple" (col B) to the last column in the sheet.

This is what I'm trying, which doesn't work. The problem I believe is with setting the CriteriaRange. Can anyone help out?

Code:
With wsSheet 
     ' Add the filter criteria
     ' column heading (e.g. Fruit)
    .Range("IU1").Value = .Range("B1") 
     ' item selected from a combobox (e.g. Apple)
    .Range("IU2").Value = frmOptions.cboFrom.Value 
     
     ' Define the range for the Advanced Filter (e.g. Brand).
    Set rngData = .Range(.Range("G1"), .Range("G65536").End(xlUp)) 
     
     ' Create a unique list and copy to Last column.
    rngData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _ 
    .Range("IU1:IU2"), CopyToRange:=Range("IV1"), Unique:=True 
     
     ' Read the filtered unique list into a variant array.
    varData = .Range(.Range("IV2"), .Range("IV65536").End(xlUp)).Value 
     
     ' Delete the temporarily created unique list.
    .Range(.Range("IV1"), .Range("IV65536").End(xlUp)).ClearContents 
    .Range("IU1:IU2").ClearContents 
End With
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi

Are the Range("B1") and Range("G1") same?

They should be labels.

Otherwise I can not see any problem.
 
Upvote 0
Skippy said:
I'm struggling with the advanced filter using VBA.

In my case, the user selects an item from a ComboBox (e.g. "Apple"). Then, from a worksheet, I want to copy all unique "Brands" from Col G that match "Apple" (col B) to the last column in the sheet.

This is what I'm trying, which doesn't work. The problem I believe is with setting the CriteriaRange. Can anyone help out?

Are they control toolbox comboboxes, or forms comboboxes?

If they're from the control toolbox, what you're doing would probably work, if the Linked Cell property of each box is linked to a cell in Column B.
If they're forms comboboxes, you'll have to right-click the control, go to Format Control, and select the Cell Link.

Remember that a combobox (or any other ActiveX control) doesn't attach itself to a cell (unless you make it that way, by using its properties or by VBA code). It might look like it's on top if a particular cell, but it's not part of the cell object.
 
Upvote 0
Thanks for the reply jindon. Not sure what you are asking.

Range("B1") = "Fruit"
Range("G1") = "Brand"

What the code does is set:
Code:
.Range("IU1").Value = "Fruit"
.Range("IU2").Value = "Apple
 rngData = .Range(.Range("G1"), .Range("G65536").End(xlUp)) 'Retrieves the Brand names for "Brands"
Then I try to apply the AdvancedFilter where CriteriaRange = "Apple" and the Unique range is defined by rngData
 
Upvote 0
Skippy said:
What the code does is set:
Code:
.Range("IU1").Value = "Fruit"
.Range("IU2").Value = "Apple
 rngData = .Range(.Range("G1"), .Range("G65536").End(xlUp)) 'Retrieves the Brand names for "Brands"
Then I try to apply the AdvancedFilter where CriteriaRange = "Apple" and the Unique range is defined by rngData

I'm not understanding how you're transferring the value from the combobox to B1 (which gets copied to Cell IU1) for use in the filter.

Is there more code that does this?
 
Upvote 0
Tazguy37 said:
Code:
.Range("IU1").Value = "Fruit"
.Range("IU2").Value = "Apple
 rngData = .Range(.Range("G1"), .Range("G65536").End(xlUp)) 'Retrieves the Brand names for "Brands"
I'm not understanding how you're transferring the value from the combobox to B1 (which gets copied to Cell IU1) for use in the filter.

Is there more code that does this?
These are just examples. The actual code in my original post for these two lines are:
Code:
     ' Add the filter criteria 
     ' column heading (e.g. Fruit) 
    .Range("IU1").Value = .Range("B1") 
     ' item selected from a combobox (e.g. Apple) 
    .Range("IU2").Value = frmOptions.cboFrom.Value
. I believe my problem is how I'm setting the CriteriaRange:
Code:
     ' Create a unique list and copy to Last column. 
    rngData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _ 
    .Range("IU1:IU2"), CopyToRange:=Range("IV1"), Unique:=True
 
Upvote 0

Forum statistics

Threads
1,214,525
Messages
6,120,052
Members
448,940
Latest member
mdusw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top