Excel Macro

[AQ Mahomed]

Hi

I need to write a macro that will do the following :

Example
Input

Col 1 Col2 COl3 Col4 Col5 Col6
StockCode 010 020 030 040 050

1818560 1 3 5
1818620 1 2
1818630 3 6 2
1818530 3 1 3
1956220 4 3 2 3

After Macro Out Put

New Sheet
Col1
StockCode
1818560010 1
1818560020 3
1818560030 5
1818620010 1
1818620020 2
1818630010 3
1818630020 6
1818630030 2
1818530010 3
1818530020 1
1818530030 3
1956220010 4
1956220020 3
1956220040 2
1956220050 3

Please be advise that i have approximately 10000 rows to convert.

I urgently require some help

Many Thanks
AQ



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Don Guillett]

try this loop within a loop
Sub rearrageem()'put cursor on new column (H)
mc = ActiveCell.Column
For Each c In Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)
For i = 2 To 6
lr = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row + 1
If Cells(c.Row, i) <> "" Then
Cells(lr, mc) = c & Cells(1, i)
Cells(lr, mc + 1) = Cells(c.Row, i)
End If
Next i
Next c
End Sub

 

[Anson]

I love short exercises like this. Try this:

'--------------------------------------------------------------------

Sub ConvertTable()
'Convert a N by M table from Sheet1 to a K by 2 table on Sheet2

Dim SPivot As Range, DPivot As Range 'Source and Designation
Dim iCol As Integer, iRow As Integer 'Column and Row Counts
Dim iList As Integer 'Count number of row on list

'Set Source Pivot at Stock Code line column 1
Set SPivot = ThisWorkbook.Sheets("Sheet1").[A2]

'Set Designation Pivot on Sheet 2 Cell A1
Set DPivot = ThisWorkbook.Sheets("Sheet2").[A1]

'Clear previous report
DPivot.Offset(1, 0).CurrentRegion.ClearContents

'Reset column heading of designation
DPivot.Offset(0, 0) = "Column 1"
DPivot.Offset(0, 1) = "Column 2"

iRow = 1 'Assumming no blank rows between headings and data
iList = 1 'Assumming the designation list starts from row 2

'Run through each row
Do While IsEmpty(SPivot.Offset(iRow, 0)) = False
iCol = 1

'Run through each column
Do While IsEmpty(SPivot.Offset(0, iCol)) = False

'Collect data only if the selected cell is not blank
If IsEmpty(SPivot.Offset(iRow, iCol)) = False Then
'Increase list size
iList = iList + 1

'Column 1 data
DPivot.Offset(iList, 0) = SPivot.Offset(0, iCol) & _
SPivot.Offset(iRow, 0)

'Column 2 Data
DPivot.Offset(iList, 1) = SPivot.Offset(iRow, iCol)
End If
iCol = iCol + 1
Loop
iRow = iRow + 1
Loop

End Sub
'------------------------------------------------------------