Using VBA to Reference a Dynamic Range based on Last Cell of an Adjacent Column

Hello,

I am currently trying to refer to a cell range defined as being from Cell K2 down to the same row as the last cell in Column J (e.g. If the last cell in Column J is J1455, the range would be "K2:K1455").

I have tried using the following syntax but keep getting an error (Runtime Error 1004: Method 'Range' of object '_Worksheet' failed):

Set rMonthRng = Sheet2.Range("J2", Range("J65536").End(xlUp)).Offset(0, 1)

I don't understand this as I have used the same structure previously in other workbooks and it worked fine.

Is anyone able to explain to me what I am doing wrong?

Thank you!

Hi, PW11,

try either

With Sheet2
  Set rMonthRng = .Range("J2", .Range("J" & Rows.Count).End(xlUp)).Offset(0, 1)
End With

or

With Sheet2
  Set rMonthRng = .Range("J2", .Range("J" & Rows.Count).End(xlUp).Offset(0, 1))
End With

Ciao,
Holger

Hi, PW11,

Sheet2.Range("J2",

This part is related to the sheet with the codename Sheet2 while

Range("J65536").End(xlUp)

relates to the ActiveSheet. As long as ActiveSheet is Sheet2 there should be no problem, otherwise the range object would require you to build two areas of this range: one on the ActiveSheet, one on Sheet2.

HTH,
Holger