VBA Help - analyzing rows to create a single column

[jerryr0125]

Hi - I am looking for a macro/vba that would do the following :
* Analyze each Row
* In another sheet create two columns
- column one would contain the Unique ID (column A from the data sheet)
- column two would contain the value in a given column on that row
* the result would be two columns - column one with the unique ID and column two the with values

Example :
Row one has Column A (the unique ID) "ABC". Column B contains "55" and Column D contains "66".
The macro would execute and add the following to a sheet in the workbook

Column A (Unique ID) , Column B (Result)
ABC , 55
ABC , 66

basically taking the one row and creating two row since it has two values.
There could be up to 30 columns to analyze and 1000's of rows.

see attachment for example data and desired result.

thoughts ? thanks - jerry

[mdmackillop]

Hi Jerry
Give this a try

Sub Test()Dim r As Range, rw
Dim arr
Dim cel
Dim i As Long, x As Long, y As Long, z As Long


x = Cells(Rows.Count, 1).End(xlUp).Row
y = Cells(1, Columns.Count).End(xlToLeft).Column
Set r = Range(Cells(2, 2), Cells(x, y))
z = Application.CountA(r) - 1
ReDim arr(0 To z, 1)
For Each rw In r.Rows
For Each cel In rw.SpecialCells(xlCellTypeConstants)
arr(i, 0) = Cells(cel.Row, 1)
arr(i, 1) = cel.Value
i = i + 1
Next cel
Next rw
Sheets("Result").Cells(2, 1).Resize(z + 1, 2) = arr
End Sub

[jerryr0125]

Perfect solution - thank you !!