Sleeper: Loop through range to copy/paste hyperlink

[Bob Phillips]

Ingenious. Thanks so much for making sense of it. i was thinking of going down rather than up, but you make a great point about the blank cells.

There is a somewhat analagous situation when inserting or deleting rows.

If you start at the top and work you way down, the pointers can get confused, and so you may not process some lines. So it better to work bottom up in these cases.

So for instance, put these values in A1:A8 - 17,1,17,2,17,17,17,3, then run this code

Sub InsertRows()
Dim iLastRow As Long
Dim i As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "A").Value = 17 Then
Cells(i, "A").EntireRow.Insert
End If
Next i
End Sub

We were supposed to insert a blank row abover every 17, not quite what we achieved ).

Restore the data, make this minor change to the code

Sub InsertRows()
Dim iLastRow As Long
Dim i As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value = 17 Then
Cells(i, "A").EntireRow.Insert
End If
Next i
End Sub

and run it again. Success!