CODEWITHSUNDEEP
excelvbaloops
programmierboy
programmierboy

Reputation: 29

VBA - Loop through column and insert a row

I just want to insert a new line in each row of a column (beginning with the third row below the header). But instead, the macro adds n-new columns in the third row, n being the number of rows in the column. I have another macro with the same loop to insert a hyperlink in each cell, which works absolute fine, now I am drawing a blank...

What could be the reason?

Sub InsertRows()

    Dim i As Integer
    Dim lRow As Integer
    lRow = Cells(Rows.Count, 3).End(xlUp).Row
    i = 3  

    Do

    Range("C" & i).Select


    ActiveCell.EntireRow.Insert

    i = i + 1
    Loop Until i > lRow


End Sub

Upvotes: 0

Views: 635

Answers (1)

T.M.
T.M.

Reputation: 9948

Assuming you want to insert a new row at the beginning of any existing row and following the existing code logic, you could try the following (I don't pretend this to be the best way to do so):

Sub InsertRows()

'Fully qualify your Range references
With Sheet1                 ' Using e.g. the project sheet's Code(Name)
    Dim lRow As Long
    lRow = .Cells(.Rows.Count, 3).End(xlUp).Row
    Dim i As Long
    i = 3
    'Define the resulting end row
    Dim eRow As Long
    eRow = 2 * lRow - i + 1

    Do
        .Range("C" & i).EntireRow.Insert
        i = i + 2                   ' increment in double steps
    Loop Until i > eRow
End With

End Sub

Upvotes: 1

Related Questions