Forum Discussion

EdBogel's avatar
EdBogel
Copper Contributor
Aug 08, 2022
Solved

Copy data for a varying number of rows with VBA

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.

excel
Macros and VBA
  • OliverScheurich's avatar
    OliverScheurich
    Aug 08, 2022

    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 Replies

  • JakeArmstrong's avatar
    JakeArmstrong
    Icon for Microsoft rankMicrosoft
    Aug 08, 2022
    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.
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Aug 08, 2022

    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.

Resources