Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose Rows to a single a Column one below other
Hello All,
I am using Office XP and have the following problem. My data is in the following format: A B C D E F G H I J..... 1 2 3 4 5 6 7 8 9 10.... 11 12 13 14 15 16 17 18 19 20 ..... ..... .... 51 52 53 54 55 56 57 58 59 60 ..... I wish to have the data in rows to be transposed on Sheet2, Col A as follows: A B C 1 2 3 4 5 6 7 8 9 10 <blank row 11 12 13 14 15 16 17 18 19 20 <blank row ..... ..... I have checked using Copy/Paste Special/Transpose but the result come side by side. My requirement is to have it in a single Col A on Sheet 2. Can this be achieved. I can make the selection prior to running the macro. Thanks to all in advance Rashid |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose Rows to a single a Column one below other
Hi Rashid,
Here's some code Sub Reorganise() Dim cCols As Long Dim cRows As Long Dim i As Long Dim j As Long With Selection cRows = .Rows.Count + .Row - 1 cCols = .Columns.Count + .Column - 1 For i = cRows To .Row Step -1 For j = .Column + 1 To cCols Cells(i + j - .Column, .Column).Value = Cells(i, j).Value Next j Cells(i, .Column + 1).Resize(1, cCols - .Column).ClearContents If i < .Row Then Cells(i, .Column).Resize(cCols - .Column + 1, 1).EntireRow.Insert End If Next i End With End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Rashid Khan" wrote in message ... Hello All, I am using Office XP and have the following problem. My data is in the following format: A B C D E F G H I J..... 1 2 3 4 5 6 7 8 9 10.... 11 12 13 14 15 16 17 18 19 20 .... .... ... 51 52 53 54 55 56 57 58 59 60 .... I wish to have the data in rows to be transposed on Sheet2, Col A as follows: A B C 1 2 3 4 5 6 7 8 9 10 <blank row 11 12 13 14 15 16 17 18 19 20 <blank row .... .... I have checked using Copy/Paste Special/Transpose but the result come side by side. My requirement is to have it in a single Col A on Sheet 2. Can this be achieved. I can make the selection prior to running the macro. Thanks to all in advance Rashid |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose Rows to a single a Column one below other
Hi Bob,
Thanks for the Code. It works fine... But I want it to copy the matter on Sheet2. Your code work on Sheet1. Could u pls amend it for copying it on Sheet2. Thanks for your time and help Rashid Khan "Bob Phillips" wrote in message ... Hi Rashid, Here's some code Sub Reorganise() Dim cCols As Long Dim cRows As Long Dim i As Long Dim j As Long With Selection cRows = .Rows.Count + .Row - 1 cCols = .Columns.Count + .Column - 1 For i = cRows To .Row Step -1 For j = .Column + 1 To cCols Cells(i + j - .Column, .Column).Value = Cells(i, j).Value Next j Cells(i, .Column + 1).Resize(1, cCols - .Column).ClearContents If i < .Row Then Cells(i, .Column).Resize(cCols - .Column + 1, 1).EntireRow.Insert End If Next i End With End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Rashid Khan" wrote in message ... Hello All, I am using Office XP and have the following problem. My data is in the following format: A B C D E F G H I J..... 1 2 3 4 5 6 7 8 9 10.... 11 12 13 14 15 16 17 18 19 20 .... .... ... 51 52 53 54 55 56 57 58 59 60 .... I wish to have the data in rows to be transposed on Sheet2, Col A as follows: A B C 1 2 3 4 5 6 7 8 9 10 <blank row 11 12 13 14 15 16 17 18 19 20 <blank row .... .... I have checked using Copy/Paste Special/Transpose but the result come side by side. My requirement is to have it in a single Col A on Sheet 2. Can this be achieved. I can make the selection prior to running the macro. Thanks to all in advance Rashid |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose Rows to a single a Column one below other
Sub Reorganise()
Dim cCols As Long Dim cRows As Long Dim i As Long Dim j As Long Dim oTarget As Worksheet Set oTarget = Worksheets("Sheet2") With Selection cRows = .Rows.Count + .Row - 1 cCols = .Columns.Count + .Column - 1 For i = cRows To .Row Step -1 For j = 1 To cCols oTarget.Cells((i - 1) * 11 + j, 1).Value = Cells(i, j).Value Next j Next i End With End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Rashid Khan" wrote in message ... Hi Bob, Thanks for the Code. It works fine... But I want it to copy the matter on Sheet2. Your code work on Sheet1. Could u pls amend it for copying it on Sheet2. Thanks for your time and help Rashid Khan "Bob Phillips" wrote in message ... Hi Rashid, Here's some code Sub Reorganise() Dim cCols As Long Dim cRows As Long Dim i As Long Dim j As Long With Selection cRows = .Rows.Count + .Row - 1 cCols = .Columns.Count + .Column - 1 For i = cRows To .Row Step -1 For j = .Column + 1 To cCols Cells(i + j - .Column, .Column).Value = Cells(i, j).Value Next j Cells(i, .Column + 1).Resize(1, cCols - ..Column).ClearContents If i < .Row Then Cells(i, .Column).Resize(cCols - .Column + 1, 1).EntireRow.Insert End If Next i End With End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Rashid Khan" wrote in message ... Hello All, I am using Office XP and have the following problem. My data is in the following format: A B C D E F G H I J..... 1 2 3 4 5 6 7 8 9 10.... 11 12 13 14 15 16 17 18 19 20 .... .... ... 51 52 53 54 55 56 57 58 59 60 .... I wish to have the data in rows to be transposed on Sheet2, Col A as follows: A B C 1 2 3 4 5 6 7 8 9 10 <blank row 11 12 13 14 15 16 17 18 19 20 <blank row .... .... I have checked using Copy/Paste Special/Transpose but the result come side by side. My requirement is to have it in a single Col A on Sheet 2. Can this be achieved. I can make the selection prior to running the macro. Thanks to all in advance Rashid |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose Rows to a single a Column one below other
Hi Bob,
Thanks for the quick response. But this macro copies the Column with '3 Blank Rows' in between.. The previous one used to insert on a Single Blank Row in between.. Something is missing ? Thanks for your time and help Rashid "Bob Phillips" wrote in message ... Sub Reorganise() Dim cCols As Long Dim cRows As Long Dim i As Long Dim j As Long Dim oTarget As Worksheet Set oTarget = Worksheets("Sheet2") With Selection cRows = .Rows.Count + .Row - 1 cCols = .Columns.Count + .Column - 1 For i = cRows To .Row Step -1 For j = 1 To cCols oTarget.Cells((i - 1) * 11 + j, 1).Value = Cells(i, j).Value Next j Next i End With End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Rashid Khan" wrote in message ... Hi Bob, Thanks for the Code. It works fine... But I want it to copy the matter on Sheet2. Your code work on Sheet1. Could u pls amend it for copying it on Sheet2. Thanks for your time and help Rashid Khan "Bob Phillips" wrote in message ... Hi Rashid, Here's some code Sub Reorganise() Dim cCols As Long Dim cRows As Long Dim i As Long Dim j As Long With Selection cRows = .Rows.Count + .Row - 1 cCols = .Columns.Count + .Column - 1 For i = cRows To .Row Step -1 For j = .Column + 1 To cCols Cells(i + j - .Column, .Column).Value = Cells(i, j).Value Next j Cells(i, .Column + 1).Resize(1, cCols - .Column).ClearContents If i < .Row Then Cells(i, .Column).Resize(cCols - .Column + 1, 1).EntireRow.Insert End If Next i End With End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Rashid Khan" wrote in message ... Hello All, I am using Office XP and have the following problem. My data is in the following format: A B C D E F G H I J..... 1 2 3 4 5 6 7 8 9 10.... 11 12 13 14 15 16 17 18 19 20 .... .... ... 51 52 53 54 55 56 57 58 59 60 .... I wish to have the data in rows to be transposed on Sheet2, Col A as follows: A B C 1 2 3 4 5 6 7 8 9 10 <blank row 11 12 13 14 15 16 17 18 19 20 <blank row .... .... I have checked using Copy/Paste Special/Transpose but the result come side by side. My requirement is to have it in a single Col A on Sheet 2. Can this be achieved. I can make the selection prior to running the macro. Thanks to all in advance Rashid |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose Rows to a single a Column one below other
I only get one?
-- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Rashid Khan" wrote in message ... Hi Bob, Thanks for the quick response. But this macro copies the Column with '3 Blank Rows' in between.. The previous one used to insert on a Single Blank Row in between.. Something is missing ? Thanks for your time and help Rashid "Bob Phillips" wrote in message ... Sub Reorganise() Dim cCols As Long Dim cRows As Long Dim i As Long Dim j As Long Dim oTarget As Worksheet Set oTarget = Worksheets("Sheet2") With Selection cRows = .Rows.Count + .Row - 1 cCols = .Columns.Count + .Column - 1 For i = cRows To .Row Step -1 For j = 1 To cCols oTarget.Cells((i - 1) * 11 + j, 1).Value = Cells(i, j).Value Next j Next i End With End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Rashid Khan" wrote in message ... Hi Bob, Thanks for the Code. It works fine... But I want it to copy the matter on Sheet2. Your code work on Sheet1. Could u pls amend it for copying it on Sheet2. Thanks for your time and help Rashid Khan "Bob Phillips" wrote in message ... Hi Rashid, Here's some code Sub Reorganise() Dim cCols As Long Dim cRows As Long Dim i As Long Dim j As Long With Selection cRows = .Rows.Count + .Row - 1 cCols = .Columns.Count + .Column - 1 For i = cRows To .Row Step -1 For j = .Column + 1 To cCols Cells(i + j - .Column, .Column).Value = Cells(i, j).Value Next j Cells(i, .Column + 1).Resize(1, cCols - .Column).ClearContents If i < .Row Then Cells(i, .Column).Resize(cCols - .Column + 1, 1).EntireRow.Insert End If Next i End With End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Rashid Khan" wrote in message ... Hello All, I am using Office XP and have the following problem. My data is in the following format: A B C D E F G H I J..... 1 2 3 4 5 6 7 8 9 10.... 11 12 13 14 15 16 17 18 19 20 .... .... ... 51 52 53 54 55 56 57 58 59 60 .... I wish to have the data in rows to be transposed on Sheet2, Col A as follows: A B C 1 2 3 4 5 6 7 8 9 10 <blank row 11 12 13 14 15 16 17 18 19 20 <blank row .... .... I have checked using Copy/Paste Special/Transpose but the result come side by side. My requirement is to have it in a single Col A on Sheet 2. Can this be achieved. I can make the selection prior to running the macro. Thanks to all in advance Rashid |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose Rows to a single a Column one below other
Hi Bob,
I still that three. Am I doning something wrong? Rashid Khan "Bob Phillips" wrote in message ... I only get one? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Rashid Khan" wrote in message ... Hi Bob, Thanks for the quick response. But this macro copies the Column with '3 Blank Rows' in between.. The previous one used to insert on a Single Blank Row in between.. Something is missing ? Thanks for your time and help Rashid "Bob Phillips" wrote in message ... Sub Reorganise() Dim cCols As Long Dim cRows As Long Dim i As Long Dim j As Long Dim oTarget As Worksheet Set oTarget = Worksheets("Sheet2") With Selection cRows = .Rows.Count + .Row - 1 cCols = .Columns.Count + .Column - 1 For i = cRows To .Row Step -1 For j = 1 To cCols oTarget.Cells((i - 1) * 11 + j, 1).Value = Cells(i, j).Value Next j Next i End With End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Rashid Khan" wrote in message ... Hi Bob, Thanks for the Code. It works fine... But I want it to copy the matter on Sheet2. Your code work on Sheet1. Could u pls amend it for copying it on Sheet2. Thanks for your time and help Rashid Khan "Bob Phillips" wrote in message ... Hi Rashid, Here's some code Sub Reorganise() Dim cCols As Long Dim cRows As Long Dim i As Long Dim j As Long With Selection cRows = .Rows.Count + .Row - 1 cCols = .Columns.Count + .Column - 1 For i = cRows To .Row Step -1 For j = .Column + 1 To cCols Cells(i + j - .Column, .Column).Value = Cells(i, j).Value Next j Cells(i, .Column + 1).Resize(1, cCols - .Column).ClearContents If i < .Row Then Cells(i, .Column).Resize(cCols - .Column + 1, 1).EntireRow.Insert End If Next i End With End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Rashid Khan" wrote in message ... Hello All, I am using Office XP and have the following problem. My data is in the following format: A B C D E F G H I J..... 1 2 3 4 5 6 7 8 9 10.... 11 12 13 14 15 16 17 18 19 20 .... .... ... 51 52 53 54 55 56 57 58 59 60 .... I wish to have the data in rows to be transposed on Sheet2, Col A as follows: A B C 1 2 3 4 5 6 7 8 9 10 <blank row 11 12 13 14 15 16 17 18 19 20 <blank row .... .... I have checked using Copy/Paste Special/Transpose but the result come side by side. My requirement is to have it in a single Col A on Sheet 2. Can this be achieved. I can make the selection prior to running the macro. Thanks to all in advance Rashid |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose Rows to a single a Column one below other
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose Rows to a single a Column one below other
Rashid,
Haven't received it yet! Right email address. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Rashid Khan" wrote in message ... Hi Bob, I have mailed you the Sample Worksheet. Pls have a look on your email . Many thanks for your time and help Rashid Khan |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose Rows to a single a Column one below other
Sent it again today.
Rashid "Bob Phillips" wrote in message ... Rashid, Haven't received it yet! Right email address. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Rashid Khan" wrote in message ... Hi Bob, I have mailed you the Sample Worksheet. Pls have a look on your email . Many thanks for your time and help Rashid Khan |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose Rows to a single a Column one below other
Got it, correction posted.
Bob "Rashid Khan" wrote in message ... Sent it again today. Rashid "Bob Phillips" wrote in message ... Rashid, Haven't received it yet! Right email address. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Rashid Khan" wrote in message ... Hi Bob, I have mailed you the Sample Worksheet. Pls have a look on your email . Many thanks for your time and help Rashid Khan |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Thanks! Transpose Rows to a single a Column one below other
Hi Bob,
Works like a magic wand. Thanks a lot. Rashid Khan "Bob Phillips" wrote in message ... Got it, correction posted. Bob "Rashid Khan" wrote in message ... Sent it again today. Rashid "Bob Phillips" wrote in message ... Rashid, Haven't received it yet! Right email address. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Rashid Khan" wrote in message ... Hi Bob, I have mailed you the Sample Worksheet. Pls have a look on your . Many thanks for your time and help Rashid Khan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose Column to Rows | Excel Discussion (Misc queries) | |||
Transpose every 10 cells in single column to multiple rows | Excel Discussion (Misc queries) | |||
Transpose data from many horizontal rows into a single column | Excel Discussion (Misc queries) | |||
transpose 3d cells to a column in single workbook | Excel Discussion (Misc queries) | |||
Transpose unique values in one column/mult. rows into a single row | Excel Worksheet Functions |