How to loop through cells in macro

[Andrew]

I have a basic macro (compliments of macro builder) but
want to repeat the same steps x times.

My macro is cutting, pasting, and deleting rows in a
prospect database. For each contact I have 4 rows of data
and want to consolidate to 1 row, and then delete the
three blank rows. Then move onto the next contact record.

How do I loop the steps or use the Dim counter to cycle
through 1000 rows?

I'd appreciate any good resources for a beginner.

Thanks in advance.
Andrew

 

[Tom Ogilvy]

Assuming your data is in column A, rows 1 to 1000

Sub ConsolidateData()
Dim i As Long
Dim rng As Range
For i = 1 To 1000 Step 4
Cells(i, 2).Value = Cells(i + 1, 1).Value
Cells(i, 3).Value = Cells(i + 2, 1).Value
Cells(i, 4).Value = Cells(i + 3, 1).Value
If rng Is Nothing Then
Set rng = Cells(i, 1).Offset(1, 0).Resize(3, 1)
Else
Set rng = Union(rng, Cells(i, 1).Offset(1, 0).Resize(3, 1))
End If
Next
rng.EntireRow.Delete
End Sub