VBA for cell range copy paste loop (excel)

Question

I have an address list that was used for labels, so it was typed as follows. A1:A3

john doe
123 happy st
happytown, ca. 12345
Jan Doe
456 sunshine ave
happytown, ca. 12345

I want to move this to a list with name address city as below A1, B1, C1

John doe	123 happy st	happytown, ca. 12345

There are about 1000 addresses so i was trying to get a quick vba code to do this as a loop until done. I got stuck on the increasing the range to capture A1:A3, copy, paste special transpose, then pick the next address which starts at A5.

i am a beginner at best but have been able to manipulate past codes to work for me. This one from scratch has proven my need to study more on this.

Any help is appreciated.

Answer 1

This worked great. Thank you so much for the assistance.

Answer 2

To:  pakalolo13

re:  more better

Here is another way, only slightly tested.

You can use any number of rows per name.

Blank rows between groups are required.

Multiple blank rows generate blank rows in the result (see picture).

As before, select the data before running the code.

Code (check for wordwrap)...

'---

Sub TransposeGroups()

Dim rCol As Long

Dim lngStep   As Long

Dim rngCell   As Excel.Range

Dim rngToMove As Excel.Range

Set rngToMove = Selection.Columns(1).Cells

rCol = 2

lngStep = 1

For Each rngCell In rngToMove

  If VBA.Len(rngCell) > 1 Then

    rngCell.Copy Destination:=rngCell.Parent.Cells(rngToMove.Rows(lngStep).Row, rngCell(1, rCol).Column)

    rCol = rCol + 1

 Else

    rCol = 2

    lngStep = lngStep + 1

  End If

Next 'rngcell

Set rngToMove = Nothing

End Sub

'---

Free Custom_Functions add-in (19 new functions) at MediaFire...

http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

Answer 3

Thanks for this code. It did work for that mail group i had. I do have another list that is not strictly 3 line type addresses. Is there some variable that can be used to count the lines, stop at the blank row and move to the next cell with content and repeat the copy, paste special transpose operation. 

Any and all help is appreciated!

Example of desired results - first column is data i have. following columns is what i would like the result to be.

Answer 4

To:  pakalolo13

re:   transposing with VBA

Select the column with the addresses before running the code.

There must not be any blank rows.

Try the code on test data before using it on your address list.

Make a backup copy of your original data, before running the code.

'---

Sub GetThingsStarted()

Call BunchAndMove(3)

End Sub

'Transposes every X rows into single adjacent row

Sub BunchAndMove(Optional ByRef lngStep As Long = 3)

Dim rngToMove As Excel.Range

Dim N    As Long

Dim M    As Long

Dim rCol As Long

Set rngToMove = Selection.Columns(1).Cells

rCol = 1

For N = 1 To rngToMove.Count Step lngStep

  M = M + 1

  With rngToMove

   .Parent.Range(.Cells(M, rCol + 1), .Cells(M, rCol + lngStep)).Value = _

    Application.WorksheetFunction.Transpose(rngToMove(N).Resize(lngStep, 1).Value)

  End With

Next 'N

Set rngToMove = Nothing

End Sub

'---

Free Custom_Functions add-in (19 new functions) at MediaFire...

http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents