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

 

[mart37]

ws.cells(2, 2).currentregion.offset(1).clearcontents

 

[rhombus4]

That's what it already is. sorry it must have put a space when I typed it

 

[rhombus4]

Does anyone know why the advanced filtering works on the sheet when run normally, but when the advanced filter is run when combobox changes I get the error

Combobox change transfers value to criteria range, runs the advanced filter then updates the row source of other combo boxes by dynamic formula in named range

 

[mart37]

A combobox returns a string as value. Maybe the criteria cells need an other datatype.

 

[rhombus4]

A combobox returns a string as value. Maybe the criteria cells need an other datatype.

I actually have 5 comboboxes but just put code for 2 as they are identical apart from where they transfer to sheet

The first combobox change works in the background to filter the Data but when i select an option in another combobox I get the errors
Think its to do with the Row Source, as when I comment out that the filter runs ok

These are the Userform Codes

Private Sub UserForm_Initialize()

Sheet1.Range("V2:Z2").ClearContents
myFilter

End Sub

Private Sub cmb1_Change()
Sheet1.Range("v2") = Me.cmb1.Value
myFilter
Row_Sources

End Sub

Private Sub cmb2_Change()
Sheet1.Range("w2") = Me.cmb2.Value
myFilter
Row_Sources

End Sub

Sub Row_Sources()
' Tried both ways below to set Row Sources
Me.cmb1.RowSource = "rsCombo1" ' is =OFFSET(Sheet1!$K$2,,,COUNTA(Sheet1!$K$2:$K$100),1)
Me.cmb2.RowSource = "rsCombo2" ' is =OFFSET(Sheet1!$L$2,,,COUNTA(Sheet1!$L$2:$L$100),1)


'Me.cmb1.RowSource = vbNullString
'Me.cmb1.RowSource = Sheet1.Range("K2", Sheet1.Range("K9999").End(xlUp)).Address

'Me.cmb2.RowSource = vbNullString
'Me.cmb2.RowSource = Sheet1.Range("L2", Sheet1.Range("L9999").End(xlUp)).Address

End Sub

This is my filter Code in module 1. get same error if I put the code in the Userform Code

Sub myFilter()
Application.ScreenUpdating = False
  Dim ws As Worksheet
  Set ws = Sheet1
    
    'ws.Cells(2, 11).CurrentRegion.Offset(1, 0).ClearContents ' Unable to get the currentregion property of the Range class
    
'If I comment out line above I get same error Unable to get the currentregion property of the Range class
    ws.Range("B1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=ws.Range("V1:Z2"), _
            CopyToRange:=ws.Range("K1:O1"), Unique:=False
            
With ws ' Removes duplicates to get new Row Sources for Combo Boxes
 .Range("K1:K1000").RemoveDuplicates Columns:=1, Header:=xlYes
 .Range("L1:L1000").RemoveDuplicates Columns:=1, Header:=xlYes
 .Range("M1:M1000").RemoveDuplicates Columns:=1, Header:=xlYes
 .Range("N1:N1000").RemoveDuplicates Columns:=1, Header:=xlYes
 .Range("O1:O1000").RemoveDuplicates Columns:=1, Header:=xlYes
End With
            
Application.ScreenUpdating = True

End Sub

 

[rhombus4]

Am I overcomplicating this?

Basically I have 5 comboboxes

When any of the comboboxes are changed the value will go into a criteria area which will

1) Run the Advanced Filter
2) Each combobox row source is then changed to reflect the result of the Advanced Filter
3) Then when another combobox is changed it updates criteria are and runs advanced filter again

Thanks

 

[RoryA]

Instead of using RowSource, I'd suggest you assign the data directly using the List property - for example:

Sub Row_Sources()
Me.cmb1.List = Application.Range("rsCombo1").Value ' is =OFFSET(Sheet1!$K$2,,,COUNTA(Sheet1!$K$2:$K$100),1)
Me.cmb2.List = Application.Range("rsCombo2").Value ' is =OFFSET(Sheet1!$L$2,,,COUNTA(Sheet1!$L$2:$L$100),1)

End Sub

Make sure to clear any existing Rowsource property for those controls first.

 

[rhombus4]

Thanks.

so
cmb1.RowSource = ""
Me.cmb1.List = Application.Range("rsCombo1").Value

Would it be best to clear it in the Row_Source sub or in the combobox change

and does it make a difference if I use

cmb1.RowSource = vbNullString or
cmb1.RowSource = ""

 

[RoryA]

I'd put it in the Row_source sub just because they logically go together. It doesn't matter which of those you use.