Compile error using "&" in defining a range

Question

I want to copy a timesheet.

I get

Compile error: Expected: list seperator or )

for the last range.

It pinpoints the "O".

Dim OvertimeRow As Long
Dim Lastrow As Long

Lastrow = Range("B90").End(xlUp).Row

Range("I8:I" & Lastrow).Copy

Range("I8").PasteSpecial xlPasteValues

OvertimeRow = Range("I:I").Find(what:="OTBO").Row

Range("M8:O" & OvertimeRow - 1).Copy

Range("M" & Lastrow + 1).PasteSpecial xlPasteValues

Range("M" & Lastrow + 1&":"&"O" & Lastrow + 1 + OvertimeRow - 9)  '**

Do not use & in conjunction with literal numbers. It is a bad practise as you are already finding out. Make sure you explicitly convert your literal numbers to a string. Its also helpful to assign literal values to constants with meaningful names so that when you come back to this in 9 months time, you stand a chance of understanding the wizard code you are writing now. You should also be aware that '1& myString' is declaring 1 as a long integer followed by a string (which will be an error) . It is not concatenating 1 to myString as would be the case for 1 & myString. — freeflow, Feb 15 '21 at 00:09
So your last line is better written as something like ' ThisWorkbook.Worksheets().Range("M" & Cstr(Lastrow+1) & ":" & "O" & Cstr(Lastrow+1+Overtimerow-9) — freeflow, Feb 15 '21 at 00:09
Wow, I just tried it and it worked perfectly. I am so grateful to you. Thank you for learning a firstimer :) — MMJG, Feb 15 '21 at 00:17
Your problem results from an attempt to create a range name, a string, that nobody wants. Excel needs the coordinates (numbers) of first and last cells and you, well, actually couldn't care less. Therefore try specifying the first and last cell of your range. First cell = `Cells(LastRow + 1, "M")`. Last cell = `Cells(Lastrow + 1 + OvertimeRow -9, "O")`, Together = `Range(Cells(LastRow + 1, "M"),`Cells(Lastrow + 1 + OvertimeRow -9, "O"))` or `Range(Cells(LastRow, "M"),`Cells(Lastrow + OvertimeRow -9, "O")).Offset(1)` — Variatus, Feb 15 '21 at 00:17

Mathieu Guindon · Answer 1 · 2021-02-15T00:40:52.600

Range("M" & Lastrow + 1&":"&"O" & Lastrow + 1 + OvertimeRow - 9)

The problem is that the & concatenation operators aren't parsing as such.

If you type ?TypeName(1&) in the immediate pane, you get Long (?TypeName(1) otherwise says Integer) - because when it's stuck to a literal value, it's a type hint, not an operator.

To fix the compile error, simply have a space before and after all operators:

Range("M" & Lastrow + 1 & ":" & "O" & Lastrow + 1 + OvertimeRow - 9)

Compile error using "&" in defining a range

1 Answers1