Copy data for a varying number of rows with VBA

[Ed_B]

A worksheet is populated with data in specified columns, but each time the number of populated rows is different. I need a macro to identify and copy the data from the varying ranges that can exist.

Thank you for any suggestions you may have.

 

[Joe4]

Can you show us an example of what your data looks like, and let us know whether or not you want to copy ALL of the data, or just some sub-section of it?

 

[Ed_B]

The data is produced in Rows 6 to Unknown # of Rows and in Columns B - O. The data to be copied is in Columns H and I. Another copy is from Columns M, N, O.

 

[Joe4]

One thing you can do is dynamically find the last row in a column with data, and use that in your copy command.
For example, if you want to the data from columns H:I starting in row 6 down to the last row with data in column H, you could do this:

Dim lr as Long
lr = Cells(Rows.Count, "H").End(xlUp).Row
Range("H6:I" & lr).Copy
...

 

[Ed_B]

That does it...Thank you Joe4

 

[Joe4]

You are welcome.