How can i fix the "Syntax Error" on VBA listbox filtering work

August 2, 2023

Im new on VBA and im trying to create the VBA listbox filtering macro. I found an example and i transferred my data to the lists. But im getting "Syntax Error" . Why i dont know. I need to fix it, can experts help me please?

I think im getting syntax error message from this line

"Sheets("liste").Range(ls.Cells(1, 1), ls.Cells(y1, x1)).AdvancedFilter Action:=xlFilterCopy"

Private Sub TextBox1_Change()
Dim ls, rs As Worksheet
Set ls = Sheets("Liste")
Set rs = Sheets("Rapor")
x1 = ls.Range("1:1").End(xlToRight).Column
y1 = ls.Range("A250000").End(xlUp).Row
x2 = rs.Range("1:1").End(xlToRight).Column
y2 = rs.Range("A250000").End(xlUp).Row
rs.Range(rs.Cells(1, 1), rs.Cells(y2, x2)).Clear
rs.Range("N1") = ComboBox1
rs.Range("N2") = TextBox1
Sheets("liste").Range(ls.Cells(1, 1), ls.Cells(y1, x1)).AdvancedFilter Action:=xlFilterCopy
CriteriaRange:=rs.Range ("N1:N2"), CopyToRange:=rs.Range("A1"), Unique:=False
x2 = rs.Range("1:1").End(xlToRight).Column
y2 = rs.Range("A250000").End(lxup).Row

ListBox1.ColumnCount = x2
i = rs.Range(rs.Cells(1, 1), rs.Cells(y2, x2))
ListBox1.List = i
End Sub






Private Sub UserForm_Initialize()

q = Sheets("Liste").Range("1:1").End(xlToRight).Column
w = Sheets("Liste").Range("A250000").End(xlUp).Row
For a = 1 To q
ComboBox1.AddItem Sheets("Liste").Cells(1, a)
Next
ListBox1.ColumnCount = q
i = Sheets("Liste").Range(Sheets("Liste").Cells(1, 1), Sheets("Liste").Cells(w, q))
ListBox1.List = i

End Sub

Solving the syntax error

>Solution :

The syntax error is because you are missing the comma and line continuation chars (space and underscore) from the end of the line that you mention … the line needs to read

Sheets("liste").Range(ls.Cells(1, 1), ls.Cells(y1, x1)).AdvancedFilter Action:=xlFilterCopy, _

The line continuation chars tell the editor that the two physical lines are actually one long line. The alternative (but less good for code readability) is to delete the newline and have CriteriaRange:=rs.Range ("N1:N2"), CopyToRange:=rs.Range("A1"), Unique:=False continue immediately on so you would have

Sheets("liste").Range(ls.Cells(1, 1), ls.Cells(y1, x1)).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rs.Range ("N1:N2"), CopyToRange:=rs.Range("A1"), Unique:=False