macro to copy and paste data between two data cells that varies in counts of rows

[richardon]

When the macro was recorded, CTRL + SHIFT+DOWN was used to select the blank rows between two data cells within the same columns. It successfully copied 21 rows, however the macro is set to recorded 21 rows but the blanks between the start point and end point of the two data cells varies for the rest of the spreadsheet. I want this macro to be dynamic so that it not only copy and paste the the recorded number (21) of blank rows but the amount between the two data cells. Sub Copy_to_Blank_rows_below()
'
' Copy_to_Blank_rows_below Macro
' Copies the Vales in the start row to all the blank rows below; down to th Next Value in column
'
' Keyboard Shortcut: Ctrl+Shift+W
'
ActiveCell.Range("A1:B1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A21").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.End(xlDown).Select
End Sub

 

[AFPathfinder]

Try this:

    Dim i As Integer
    Dim finalRow As Integer
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To finalRow
        If Not Cells(i, 1).Value = "" Then
            Cells(i, 1).Resize(, 2).Copy
        Else
            Cells(i, 1).PasteSpecial xlPasteAll
        End If
    Next i

It is dynamic no matter how many rows you have and doesn't rely on the End(xlDown) method. There is one thing you may want to change.

finalRow = Cells(Rows.Count, 1).End(xlUp).Row

Find the column that consistently has data in the last row in your data-set. change the "1" in that line of code to the corresponding column number (ie A=1, B=2, C=3).