Too may line continuations

Question

I am trying to filter a table (columns - "A1:BB") based on a certain column (column #16) which contains several values. However, each cell in the column can contain any one particular value at any time. I am trying to check if the value in the cell matches certain values.

With Sheets("LifeTimeData")
.Range("A1:BB" & lastrow).AutoFilter field:=16, **SOME CRITERIA HERE**
.Range("A1:BB" & lastrow).AutoFilter field:=22, Criteria1:=xlFilterLastMonth

Set r = Intersect(.AutoFilter.Range, .Range("A:A"))
Filtred_Rows_Count = Application.WorksheetFunction.Subtotal(103, r) - 1
Worksheets("LifeTimeData").AutoFilterMode = False
Sheets("Overview").Range("H" & x) = Filtred_Rows_Count
End With

Criteria is as below:

Criteria1:="*" & Trim("Franchise Awarded") & "*", _
         Operator:=xlOr, Criteria2:="*" & Trim("Deposit Received") & "*", _
         Operator:=xlOr, Criteria3:="*" & Trim("Agent Awarded") & "*", _
         Operator:=xlOr, Criteria4:="*" & Trim("Approved Operator") & "*", _
         Operator:=xlOr, Criteria5:="*" & Trim("Archive non assignÃ©") & "*", _
         Operator:=xlOr, Criteria6:="*" & Trim("Audit") & "*", _
         Operator:=xlOr, Criteria7:="*" & Trim("Awarded Franchise - New") & "*", _
         Operator:=xlOr, Criteria8:="*" & Trim("Awarded Franchise - Resale") & "*", _
         Operator:=xlOr, Criteria9:="*" & Trim("Chocolate Photo Booth") & "*", _
         Operator:=xlOr, Criteria10:="*" & Trim("Client") & "*", _
         Operator:=xlOr, Criteria11:="*" & Trim("Closed") & "*", _
         Operator:=xlOr, Criteria12:="*" & Trim("Closed Deal - Deposit Agreement") & "*", _
         Operator:=xlOr, Criteria13:="*" & Trim("Closed Deal - Franchise Agreement") & "*", _
         Operator:=xlOr, Criteria14:="*" & Trim("Closed Deal - Restaurant Development Agreement") & "*", _
         Operator:=xlOr, Criteria15:="*" & Trim("Completed") & "*", _
         Operator:=xlOr, Criteria16:="*" & Trim("Completed - Blueprint Only") & "*", _
         Operator:=xlOr, Criteria17:="*" & Trim("Completed Licensee") & "*", _
         Operator:=xlOr, Criteria18:="*" & Trim("CPB Qualification form answered") & "*", _
         Operator:=xlOr, Criteria19:="*" & Trim("CPB Qualification form sent") & "*", _
         Operator:=xlOr, Criteria20:="*" & Trim("Dealer Awarded") & "*", _
         Operator:=xlOr, Criteria21:="*" & Trim("deposit recâ€™d") & "*", _
         Operator:=xlOr, Criteria22:="*" & Trim("Closed Sale") & "*", _
         Operator:=xlOr, Criteria23:="*" & Trim("DisponibilitÃ© territoire en attente") & "*"

However, this shows a warning "Too may line continuations".

Any thoughts?

Range.Autofilter only has 2 criteria arguments, `Criteria1` and `Criteria2`. You can also only specify a ***single*** parameter for `Operator`. [See the documentation here](https://msdn.microsoft.com/en-us/library/office/ff193884.aspx). — Comintern, Aug 24 '16 at 13:08

score 1 · Answer 1 · answered Aug 24 '16 at 08:35

1

For some bizarre reason, there's a limit to the number of line continuations you can have in VBA.

From memory, it's 25.

The obvious, but not particularly aesthetically pleasing, solution is to re-join some of the lines.

answered Aug 24 '16 at 08:35

Bathsheba

231,907
34
361
483

Hmm. That could be a workaround. But I tried having 3 conditions and VBA returned to me an "Application or object defined error" – Tanmoy Aug 24 '16 at 10:19
@Tanmoy - See [filter out multiple criteria using excel vba](http://stackoverflow.com/questions/28575754/filter-out-multiple-criteria-using-excel-vba). – Comintern Aug 24 '16 at 13:46

Too may line continuations

1 Answers1