How to create a loop to perform same set of action

[vishal]

Hi all,

I have a small query. I have copied certain data from a word file o
excel. The data pertains to names and addresses and is in a ver
orderly way--

name
add1
add2
city
pin
email

name
add1
add2
city
pin
email

name
add1
add2
city
pin
email

I want the data in columnar faishon. I have a list of some 400 records
Doing manually shall take a lot of time. please suggest how to write
small macro with a loop which can perform this action in a few seconds
The data is consistent in terms of rows arrangement.

regards
Visha

 

[Bob Phillips]

Hi Vishal,

Here's a macro to do it

Sub Reformat()
Dim iLastRow As Long
Dim i As Long, j As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow - 5 To 1 Step -7
For j = 6 To 2 Step -1
Cells(i, j).Value = Cells(i + j - 1, "A").Value
Next j
Cells(i + 1, "A").Resize(5.1).EntireRow.Delete
Next i
Range("A1").EntireRow.Insert
Columns("A:A").AutoFilter Field:=1, Criteria1:="="
Cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[vishal]

Hi Bob,

Thank you very much for the help. Your program virutally saved me one
full day

Regards
Visha