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

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