Excel 2013 VBA loop, how to make skip to next row

[jedwardo]

Hi Guys,

New at loops here, this is copying the data I need to a new sheet but it's all landing in the same two cells and overwriting the previous data instead of skipping to the next row down each time before pasting the next.

Sub TestSub()Dim iCell As Range
Lastrow = Sheet6.UsedRange.Rows.Count
For Each iCell In Range("A2:BD3")


If iCell.Interior.ColorIndex = 35 Then
Sheet6.Cells(Lastrow, 1).Value = iCell.Text
Sheet6.Cells(Lastrow, 1).Offset(0, 1).Value = iCell.Offset(0, 1).Text
End If


Next iCell


End Sub

 

[Jerry Sullivan]

Hi Michael,

Even if we know there will be no blanks in Col A, what caught my eye was that on each loop, the code was repeating the process of searching for the last row.

That's a relatively fast process, but it's just not necessary - especially if the color criteria wasn't met and lastrow will be unchanged.

 

[jedwardo]

Thanks Jerry, updated code.