Dynamic Ranges

[Danny54]

I have the statement below that I hardcoded the values I165:L165.

Each time I create this report I have to change the values (165 to the last row).

Is there a way to dynamically change them using xlUp since the column IXXX is the last row in column I and the L165 is the same row just over 4 cells.

Thanks


sourceBook.Sheets("Services").Range("I165:L165").Copy


Thanks

 

[Fluff]

How about

sourceBook.Sheets("Services").Range("I" & Rows.Count).End(xlUp).Resize(, 4).Copy

 

[Danny54]

how would one modify this statement to get the cell info 4 rows above this range.

I tried something like this

sourceBook.Sheets("Services").Range("I" & Rows.Count).End(xlUp).Resize(, 4).Copy <- orginal
sourceBook.Sheets("Services").Range("I" & Rows.Count - 4).End(xlUp).Resize(, 4).Copy

Thanks

 

[Fluff]

Try

sourceBook.Sheets("Services").Range("I" & Rows.Count).End(xlUp).Offset(-4).Resize(, 4).Copy

 

[Danny54]

again,
perfect.


Thanks so much!

 

[Fluff]

My pleasure & thanks for the feedback