How to Break a Long Line of Code Into Multiple Lines in VBA

user_14885721 0
Viewed 606

I have a long of code that I would like to break up in two lines. I know we usually use _ and go to the next line, but as I am breaking up a list in a nested function I get the error:

Compile error: Expected: list separator or )

The code works fine otherwise.

The line I want to split up is the second one: If Not Intersect(Target, Range [...])

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("F6:F1000000, G6:G1000000, K6:K1000000, L6:L1000000, P6:P1000000, Q6:Q1000000, U6:U1000000, V6:V1000000, Z6:Z1000000, AA6:AA1000000, AE6:AE1000000, AF6:AF1000000")) Is Nothing Then
        Application.EnableEvents = False
        If ActiveCell.Value = ChrW(&H2713) Then
            ActiveCell.ClearContents
        Else
            ActiveCell.Value = ChrW(&H2713)
        End If
        Cancel = True
    End If
    Application.EnableEvents = True
End Sub

Thank you.

vba excel
1 Answers

Instead of

Range("F6:F1000000, G6:G1000000, K6:K1000000, L6:L1000000, P6:P1000000, Q6:Q1000000, U6:U1000000, V6:V1000000, Z6:Z1000000, AA6:AA1000000, AE6:AE1000000, AF6:AF1000000")

you can also use

Range("F6:G1000000, K6:L1000000, P6:Q1000000, U6:V1000000, Z6:AA1000000, AE6:AF1000000")

This is just the same range

Another approach might be

Dim s1 As String
Dim s2 As String

s1 = "F6:F1000000, G6:G1000000, K6:K1000000, L6:L1000000, P6:P1000000, Q6:Q1000000, "
s2 = "U6:U1000000, V6:V1000000, Z6:Z1000000, AA6:AA1000000, AE6:AE1000000, AF6:AF1000000"

and then use

Range(s1 & s2)

And, of course, one could use the approach mentioned in the comments

Dim s As String

s = "F6:F1000000, G6:G1000000, K6:K1000000, L6:L1000000, P6:P1000000, Q6:Q1000000, " & _
    "U6:U1000000, V6:V1000000, Z6:Z1000000, AA6:AA1000000, AE6:AE1000000, AF6:AF1000000"
user_66009400
Related