Copy data for a varying number of rows with VBA

[EdBogel]

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.

[#2 · Accepted solution]

@EdBogel 

Sub copy()

Dim MaxRow As Long

Range("D:D").Clear

MaxRow = Range("A" & Rows.Count).End(xlUp).Row

Range("A1:A" & MaxRow).copy Destination:=Cells(1, 4)

End Sub

You can try this macro to indentify the range in column A and to copy the content to column D.

[#3]

@OliverScheurich 

Thank you...that helps a lot.

[#4]

Hey @EdBogel,

I'm not sure if it solves your exact use case, but have you considered using Dynamic Arrays?

They were designed to allow variable-sized data on the sheet, similar to your description.