Inserting An "Array" between Rows in Excel

Question

I have the below macro which inserts an array of data on each alternative row.

This works well, however, my data changes all the time so it is not the best method.

Sub inserttexteveryonerow()
    Dim Last As Integer
    Dim emptyRow As Integer
    Last = Range("A" & Rows.Count).End(xlUp).Row
    For emptyRow = Last To 2 Step -1
        If Not Cells(emptyRow, 1).Value = "" Then
            Rows(emptyRow).Resize(1).insert
            Range(Cells(emptyRow, "A"), Cells(emptyRow, "F")).Value = Array("COLA", "COLB", "COLC", "COLD", "COLD", "COLF")
        End If
    Next emptyRow
End Sub

I want to have my data on Sheet1, but my alternate insert row on Sheet2 which has the specific data.

How can I edit this row:

Range(Cells(emptyRow, "A"), Cells(emptyRow, "F")).Value = Array("COLA", "COLB", "COLC", "COLD", "COLD", "COLF")

From inserting specific data to inserting the range on SHEET2 rows A1 to AF?

YowE3K YowE3K · Accepted Answer · 2016-09-12T03:05:45

Assuming your request is to insert the range on Sheet2 from cell A1 to cell F1, the following should work:

Sub inserttexteveryonerow()
    Dim Last As Integer
    Dim emptyRow As Integer
    Last = Range("A" & Rows.Count).End(xlUp).Row
    For emptyRow = Last To 2 Step -1
        If Not Cells(emptyRow, 1).Value = "" Then
            Rows(emptyRow).Resize(1).insert
            Range(Cells(emptyRow, "A"), Cells(emptyRow, "F")).Value = Worksheets("Sheet2").Range("A1:F1").Value
        End If
    Next emptyRow
End Sub

However, it would be a good idea to rewrite your code to use a With block, which will make it easier to correctly identify which sheets you are referring to:

Sub inserttexteveryonerow()
    Dim Last As Integer
    Dim emptyRow As Integer
    With Worksheets("Sheet1")
        Last = .Range("A" & .Rows.Count).End(xlUp).Row
        For emptyRow = Last To 2 Step -1
            If Not .Cells(emptyRow, 1).Value = "" Then
                .Rows(emptyRow).Resize(1).insert
                .Range(.Cells(emptyRow, "A"), .Cells(emptyRow, "F")).Value = Worksheets("Sheet2").Range("A1:F1").Value
            End If
        Next emptyRow
    End With
End Sub

(Correctly qualifying which sheet you are referring to when using Range, Cells, etc, will prevent a lot of errors later.)

Inserting An "Array" between Rows in Excel

1 Answers