Home Ask Login Register

Developers Planet

Your answer is one click away!

fordo19 February 2016

Can't set a Dynamic Range without Error

I'm trying to select a range in a macro, but I keep getting

"Compile error:"

"Expected: list separator or )"

I'm just trying to set a dynamic range using a variable I created before

With Sheets("Recon")
rownum3 = rownum2 + 2
rownum4 = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
set r3 = .range("K"&rownum3&":K"&rownum4)

This gives me the error

Thanks in advance


BruceWayne February 2016

As silly as it sounds, you need to add white space in your range (and also add an end with)

With Sheets("Recon")
rownum3 = rownum2 + 2
rownum4 = .UsedRange.SpecialCells(xlCellTypeLastCell).Row
set r3 = .range("K" & rownum3 & ":K" & rownum4)
end with

For some reason, when using variables, even when you separate them with the &, VBA doesn't like variables next to quotes. Just add a space in between your & connectors and it should work okay.

One way to work around this non-auto-formatting issue is to use Range(cells(),cells()):

Set r3 =.Range(.Cells(rownum3,11),.cells(rownum4,11))

Cells() is Cells([row #],[col. #]), so wrapping that in a Range() is another way to do it (any my personal preferred way).

Post Status

Asked in February 2016
Viewed 2,136 times
Voted 12
Answered 1 times


Leave an answer

Quote of the day: live life