Combobox issues on userform to update advanced filter

[rhombus4]

Have an advanced filter which works when I edit values in my criteria on sheet1.

However when I enter values in a combobox which transfers a value to a cell in the criteria area I get errors when the advanced filter runs

Unable to get the current region property if the range Class on line
ws.cells(2, 2).currentregion.offset(1).clear contents

When I change it to ws.range("B2:F100").clear contents I get Clear contents method of range Class failed

 

[RoryA]

What happened to the arrayfromrange function?

 

[rhombus4]

Sorry I changed the names of the Comboboxes and when I reposted the Code I copied the one before the array part

This is updated which gives me an error when the Dynamic range after the Advanced Filter has no entries in that particular column

Me.cmb2.RowSource = ""
Me.cmb2.List = ArrayFromRange(Application.Range("rsCombo2")) '1004: Method Range of object Application failed if there are no entries in Dynamic Range

Also when I enter ?Application.Range("rsCombo2").address in Immediate window i get the same error

 

[RoryA]

What do you want to happen when there aren't any entries in the range?

 

[rhombus4]

when the combobox dropdwon is clicked to show nothing there maybe artificially make dropdown K2 or K2:K3, thought the ArrayFromRange did that

As K1 is the heading maybe something like below unless you can think of a better way

If CountA(K:K) <2 then
Me.cmb2.List = "K2:K3")
Else
Me.cmb2.List = ArrayFromRange(Application.Range("rsCombo2"))
end if

 

[RoryA]

You could use something like:

Sub Row_Sources()
On Error Resume Next
With ComboBox1
   .RowSource = ""
   .Clear
   .List = ArrayFromRange(Application.Range("rsCombo1"))
End With
With ComboBox2
   .RowSource = ""
   .Clear
   .List = ArrayFromRange(Application.Range("rsCombo2"))
end with
End Sub

or just amend the name definition so that it always returns at least one cell using MAX(1,COUNTA(...)) rather than just COUNTA(...)

 

[rhombus4]

When I used code above, I entered a value in 1st ComboBox which has entries for each column in Advanced FIlter. It cleared 1st ComboBox so didn't even run the Advance Filter

If I comment out .Clear for a combobox which didn't cause issues before it populated COmbobox 2 as it should with whatever values appear in the Advanced Filter Column
But When I enter a value in Combobox 1 which maybe hasn't got any values in the Advanced Filter, the 2nd ComboBox fills up with every value even though the column is blank


I actually have 5 comboboxes, so when combo1 is changed combo 2 3 4 and 5 should be updated with whatever appears in their respective columns in the advanced filter

If no Values appear in their respectuve columns then that particular combobox will be empty

 

[RoryA]

I’d suggest you revert the code to what it was and amend the named ranges as I suggested.

 

[rhombus4]

Thanks. Editing the dynamic code worked

Thought it was going to an easy job, turned out to be alot more complicated than I thought