(Solved) – How to copy a row (variable) and paste it into a specific cell (also row as variable) with VBA in Excel

(solved)-–-how-to-copy-a-row-(variable)-and-paste-it-into-a-specific-cell-(also-row-as-variable)-with-vba-in-excel

I’m learning VBA and I want to shorten a code I’ve written.
I want to copy a row which is a variable and paste it to the row above and then the second column. Ideally, it also pastes together row (-1,2) and (2,1) as it is a broken row in Excel.

I now have the following code:

Sub Clean()

Dim i As Long
Dim x As Long

For i = 2 To 48987

x = i - 1

If IsNumeric(Cells(i, 1)) = True Then

'Do nothing

Else

    Cells(x, 2) = Cells(x, 2)   Cells(i, 1)
    Cells(x, 3) = Cells(i, 2)
    Cells(x, 4) = Cells(i, 3)
    Cells(x, 5) = Cells(i, 4)
    Cells(x, 6) = Cells(i, 5)
    Cells(x, 7) = Cells(i, 6)
    Cells(x, 8) = Cells(i, 7)
    Cells(x, 9) = Cells(i, 8)
    Cells(x, 10) = Cells(i, 9)
    Cells(x, 11) = Cells(i, 10)
    Cells(x, 12) = Cells(i, 11)
    Cells(x, 13) = Cells(i, 12)
    Cells(x, 14) = Cells(i, 13)
    Cells(x, 15) = Cells(i, 14)
    Cells(x, 16) = Cells(i, 15)
    Rows(i).Delete
End If

Next i

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *