Transform table
Please help me with vb or macros to tranpose Table 1 to Table 2 for any
number of entries. The courses in a row should be made into only one course in a row as shown in Table 2. Table 1 Trainer Room 25-Jun-05 26-Jun-05 27-Jun-05 28-Jun-05 29-Jun-05 Trainer1 Room1 CSB00 CSB00 CSB00 Trainer2 Room2 CSB01 CSB01 Table 2 Trainer Room 25-Jun-05 26-Jun-05 27-Jun-05 28-Jun-05 29-Jun-05 Trainer1 Room1 CSB00 Trainer1 Room1 CSB00 Trainer1 Room1 CSB00 Trainer2 Room2 CSB01 Trainer2 Room2 CSB01 -- JA |
Transform table
One way:
Option Explicit Sub testme01() Dim curWks As Worksheet Dim newWks As Worksheet Dim FirstRow As Long Dim LastRow As Long Dim FirstCol As Long Dim LastCol As Long Dim iRow As Long Dim iCol As Long Dim oRow As Long Set curWks = Worksheets("sheet1") Set newWks = Worksheets.Add curWks.Rows(1).Copy _ Destination:=newWks.Range("a1") With curWks FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row FirstCol = 3 LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column oRow = 1 For iRow = FirstRow To LastRow For iCol = FirstCol To LastCol If Trim(.Cells(iRow, iCol).Value) = "" Then 'skip it, do nothing Else oRow = oRow + 1 .Cells(iRow, 1).Resize(1, 2).Copy _ Destination:=newWks.Cells(oRow, 1) newWks.Cells(oRow, iCol).Value = .Cells(iRow, iCol).Value End If Next iCol Next iRow End With newWks.UsedRange.Columns.AutoFit End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm asante_za wrote: Please help me with vb or macros to tranpose Table 1 to Table 2 for any number of entries. The courses in a row should be made into only one course in a row as shown in Table 2. Table 1 Trainer Room 25-Jun-05 26-Jun-05 27-Jun-05 28-Jun-05 29-Jun-05 Trainer1 Room1 CSB00 CSB00 CSB00 Trainer2 Room2 CSB01 CSB01 Table 2 Trainer Room 25-Jun-05 26-Jun-05 27-Jun-05 28-Jun-05 29-Jun-05 Trainer1 Room1 CSB00 Trainer1 Room1 CSB00 Trainer1 Room1 CSB00 Trainer2 Room2 CSB01 Trainer2 Room2 CSB01 -- JA -- Dave Peterson |
All times are GMT +1. The time now is 12:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com