0

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
programmierboy
  • 29
  • 3
  • What's `celltext` in `If InStr(1, celltext, "Aleman") > 0 Then`? – BigBen Jun 09 '20 at 14:06
  • Don't use `ActiveCell` either; [this question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) should be helpful. – BigBen Jun 09 '20 at 14:07
  • @BigBen Sorry, this is a left over. Next step, is checking the value for a specific string before inserting a new row. – programmierboy Jun 09 '20 at 14:23

1 Answers1

1

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
T.M.
  • 9,436
  • 3
  • 33
  • 57
  • Posted a solution to your insertion issue caused by the fact that you have to move in double steps; feel free to upv./accept by ticking the green checkmark if helpful - @programmierboy – T.M. Jun 11 '20 at 15:10