Need some code help

[Mattandy55]

I have a dynamic range on a worksheet that starts in O2-R2. There could be hundreds of rows of data or 1 row of data (only O2-R2 populated). From there I need to copy that used range to another sheet. I am using an advanced filter so the same range O2-R2 will change for each advanced filter and paste to a different tab so the ranges changes for 4 tabs. I have a code which was working, but if one of the used ranges is only in row 2 it copies down to the last row and then when it goes to paste i get a you can't paste as the copy and paste areas aren't the same.

I currently am using:

Range("o2:R2").Select
Range(Selection, Selection.End(xlDown)).Select

which works great if my data range has more data than just O2-R2. I can use this:

Range("o2:R2").Select
Range(Selection, Selection.End(xlDown).Offset(-1)).Select

but that only works if the advanced filter criteria is only populated in O2-R2. Is there a code to do what each of them are doing but be dynamic? Any help with some code that will select the range starting O2-R2 down to the last used cells in that O-R range?

Thank you

 

[Max1616]

Try this:

Sub DynamicRange()

Range("O2:R" & Cells(Rows.Count, 15).End(xlUp).Row).Select

End Sub

 

[kevin9999]

If you're not sure which column in the range O:R has the last populated cell, use this:

Range("O2:R" & Range("O:R").Find("*", , xlFormulas, , xlByRows, xlPrevious).Row).Select

 

[Mattandy55]

Thank you both! I was able to incorporate both into a couple different reports! Thank you!