Macro to convert rows to columns

Riaang

Board Regular
Joined
Aug 29, 2002
Messages
146
Hi there,

Can anyone help me to write a macro that can convert rows into columns. I have a excel spreadsheet and in column A is the member number. From B to E will be the rest of the detail. On the second line or row the member number will repeat in column A but in column B to E is different information for the member than in row 1.

I want all the information of the member to be in one line. Some member have only 2 lines whereby other members can have more than that.

Any help will be appreciated.

Thanks
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
Are you trying to transpose your data:
e.g. A B C D E
becomes
A
B
C
D
E

If so, you can accomplish this by using paste special and checking transpose.

If not, a little clarification about what your existing data looks like, and how you would like it to look is in order.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
If I understand your request, this should work.
Assumes you want to delete the (excess) rows at the same time.
Try this on a copy of your workbook and see if it's what you want.
Code:
Sub ConvertToRows()
Dim i As Long, x As Long
x = Range("A65536").End(xlUp).Row
Application.ScreenUpdating = False
For i = x To 1 Step -1
  If i <> 1 Then
    If Cells(i, 1).Value = Cells(i - 1, 1).Value Then
        Range(Cells(i, 2), Cells(i, 2).End(xlToRight)).Copy Cells(i - 1, 256).End(xlToLeft)(1, 2)
        Cells(i, 1).EntireRow.Delete
    End If
  End If
Next i
Application.ScreenUpdating = False
End Sub
Hope it helps,
Dan
 

Riaang

Board Regular
Joined
Aug 29, 2002
Messages
146
I actually want to convert the rows into columns.

eg
A
B
C
D
E
F
must change to ABCDEF.

Regards
 

Riaang

Board Regular
Joined
Aug 29, 2002
Messages
146

ADVERTISEMENT

I actually want to convert the rows into columns.

eg
A
B
C
D
E
F
must change to ABCDEF.

Regards
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Is that in response to PA HS Teacher, or me?
As I understand your question the code I posted should do that.
If I'm wrong in my interpretation, can you use Colo's HTML maker to post an example of what your data looks like and what you want it to end up looking like?

Dan
 

Riaang

Board Regular
Joined
Aug 29, 2002
Messages
146

ADVERTISEMENT

The response was to HS Teacher.

I will try your code and I'm sure it will do the trick.

Thanks a lot
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
Hello HalfAce... you may want to change the last application update to be equal to True ... :wink:

Application.ScreenUpdating = true
 

jOSEFFB

New Member
Joined
Aug 21, 2003
Messages
37
I'm having a similar problem.

I want to take a report that in in the following format

header1 header2 header3 header4... header17
data1 data2 data3 data4 ,,, data17


and turn it into

header1 header2 header6 data7 data 7 data 7 (these are Types)
data1 data2 header 12 data 12 data13 data 14

I can manually do this but I can't manage to think of how to automate it.

I've recorded some macros and this is what it gave me.

Please note that this is the first two records.

Any help appreciated.

I'll try to upload a copy of my before and after spreadsheets.

ActiveCell.Range("A1:R1").Select
Selection.Copy
Sheets("Test").Select
ActiveCell.Offset(-33, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveCell.Columns("A:A").EntireColumn.ColumnWidth = 25

ActiveCell.Offset(0, -16).Range("A1:R3").Select
Selection.Copy
Sheets("Test").Select
ActiveCell.Offset(-12, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveCell.Offset(8, 3).Range("A1").Select

Sheets("C8").Select
ActiveCell.Offset(0, -1).Range("A1:A3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Test").Select
ActiveCell.Offset(-8, -5).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, -1).Range("A1").Select
Sheets("C8").Select
ActiveCell.Offset(0, -3).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Test").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Range("A1:B1").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=ActiveCell.Range("A1:B18")
End Sub

Tell me if you need spreadsheets.


:pray:
 

Forum statistics

Threads
1,148,050
Messages
5,744,513
Members
423,881
Latest member
Nguyen Vu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top