Autofilter using VBA code getting error with condition in different cell than table?

[babaso_tawase]

I have table data like below of range a8:q20 and condition in cell E2 with combobox with dropdown value 1 to 12
Sheet name is linearity and table name is points

I used vba code

Private Sub ComboBox1_Change()
Sheet1.Unprotect Password:="a"
Dim points As Range
With Worksheets("Linearity")
Set points = .Range("E2")
With .Range("a8:q20")
.AutoFilter Field:=1, Criteria1:="<=" & points, Operator:=xlFilterValues, Visibledropdown:=False
End With
End With
Sheet1.Protect Password:="a"
End Sub


but getting error whole running code with option end or debug.
Please help in code.

Sr.no	Data1	Data2	Data3	Data4
1	abc	W3	te	Ty
2	ade		At
3	aa		Atr

 

[dermie_72]

Try changing the Dim points to string
Then remove the Set and just have points = .range("E2").Value
See if that works.

 

[babaso_tawase]

Thanks alot.

I used below vba code

Private Sub ComboBox1_Change()
Sheet1.Unprotect Password:="a"

Dim points As Range
With Worksheets("Linearity")
Set points = .Range("E14")
End With

With Worksheets("Linearity").Range("A18:s37")
.AutoFilter Field:=1, Criteria1:="<=" & CStr(points), Operator:=xlFilterValues, Visibledropdown:=False
End With

Sheet1.Protect Password:="a"

End Sub


But getting runtime error 1004 autofilter method of range class failed. Please help

 

[Alex Blakenburg]

and table name is points

What is the range of the table "points" ?
Are you trying to filter the table points or trying to use the data in points to filter the other data in which case is the other data also a table and if so what is its name ?
Maybe provide a screen shot showing row and column references of the points table and if different the other table.
What is this line trying to do Set points = .Range("E14") ?
PS: you changed the range in Post 3, which is correct ?

 

[babaso_tawase]

Thank for reply.

I here attached screen shot.

Here data changed , reference cell criteria in E2 as combo box dropdown linked cell. I just want filter and show number of rows equal to number in E2 cell. Suppose in combo box I select 7 then show rows upto 15 i.e corresponding up to number 7 in column A and hide row 16 to 20 mean number in column A is 8 to 12.
Thanks in advance.

 

[Alex Blakenburg]

Give this a try:

Private Sub ComboBox1_Change()
Sheet1.Unprotect Password:="a"

Dim critPoints As Long
Dim tblPoints As ListObject

With Worksheets("Linearity")
    critPoints = .Range("E2").Value
End With

Set tblPoints = Range("Points").ListObject

With tblPoints
    If tblPoints.ShowAutoFilter = True Then
        tblPoints.AutoFilter.ShowAllData
    End If
    
    .Range.AutoFilter Field:=1, Criteria1:="<=" & critPoints, Operator:=xlFilterValues ', Visibledropdown:=False
End With

Sheet1.Protect Password:="a"

End Sub

 

[babaso_tawase]

Sheet1.Unprotect Password:="a" Dim critPoints As Long Dim tblPoints As ListObject With Worksheets("Linearity") critPoints = .Range("E2").Value End With Set tblPoints = Range("Points").ListObject With tblPoints If tblPoints.ShowAutoFilter = True Then tblPoints.AutoFilter.ShowAllData End If .Range.AutoFilter Field:=1, Criteria1:="<=" & critPoints, Operator:=xlFilterValues ', Visibledropdown:=False End With Sheet1.Protect Password:="a"

Thanks for reply.

Working fine but error as you can not this command in protected sheet. Please suggest any correction.


Mean while I used below code but same error
Private Sub ComboBox1_Change()
Dim ws As Worksheet
Dim filterValue As Long

Set ws = ThisWorkbook.Sheets("Linearity") ' Change "Sheet1" to your actual sheet name

filterValue = ws.Range("E2").Value

If ws.AutoFilterMode Then
ws.AutoFilterMode = False
End If

With ws.Range("A9:Q20")
.AutoFilter Field:=1, Criteria1:="<=" & filterValue
End With
End Sub



But same error

 

[Alex Blakenburg]

Did you change the password in the code I sent you to the correct password ?

 

[babaso_tawase]

Did you change the password in the code I sent you to the correct password ?

no I kept now "a" only

 

[babaso_tawase]

Did you change the password in the code I sent you to the correct password ?

VBA code is working fine filtering data but I have click on end on error message box.