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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi

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

They should be labels.

Otherwise I can not see any problem.
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
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.
 

Skippy

Board Regular
Joined
Mar 3, 2002
Messages
194

ADVERTISEMENT

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
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
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?
 

Skippy

Board Regular
Joined
Mar 3, 2002
Messages
194
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,334
Messages
5,571,589
Members
412,407
Latest member
ElmerCC
Top