Adding a new row above, while adding suffix to first and last cell in new row

Question

Need to create a macro that will copy an entire row above the existing row, then deselect the copy range, and add an "A" to the end of the first and last cell in the new row.

I have gotten as far as adding the "A" in the first cell, but cannot work out how to do so for the last cell also. See code below

Private Sub CommandButton1_Click()

    InsertCopy
    ActiveCell.Select
    Application.CutCopyMode = False
    ActiveCell.Value = ActiveCell.Value & "A"

End Sub

Unknown Unknown · Accepted Answer · 2018-02-14T02:11:18

After the copy/insert your active cell and selection should be on the newly inserted row. You need to isolate the used range in that row to edit the first and last cell.

...
Application.CutCopyMode = False
dim nrw as long
nrw = activecell.row
with range(cells(nrw, "A"), cells(nrw, columns.count).end(xltoleft))
    .cells(1, 1) = .cells(1, 1).value & "A"
    .cells(1, .columns.count) = .cells(1, .columns.count).value & "A"
end with

You should also be providing a parent worksheet refrence.

Adding a new row above, while adding suffix to first and last cell in new row

1 Answers