Dear VBA Gurus,
I have adapted some code and it works almost perfectly in that it transposes data for each item in column A and details in subsequent columns on the same row and puts that in column format with the item number repeated, however, it does not work when the item starts with a 0 for example:
Column A Column B Column C Column D
12345 a-100 Blue a-233
will result in:
Column A Column B
12345 a-100
12345 Blue
12345 a-233
but if the item is 02345 or 03445 the code below won't pick it up....it ignores it and moves to the next item in the list. Also if in the example above, column B is blank but columns C, D, E, F etc are not, the code will also ignore the item and move to transpose the next items.
How would the code below need to be modified in order to address those two issues?
Thanks!
Sub test()
Dim a, b(), i As Long, ii As Long, iii As Long, n As Long
With Sheets("Sheet5")
With .Range("a1").CurrentRegion.Resize(.Cells.SpecialCells(11).Row)
a = .Value
ReDim b(1 To Application.CountA(.Cells), 1 To 2)
End With
End With
For i = 2 To UBound(a, 1)
If a(i, 1) <> "" Then
For ii = 2 To UBound(a, 2) Step 2
If (a(i, ii) <> "") * (a(i, ii + 1) <> "") Then
For iii = 0 To 1
n = n + 1
b(n, 1) = a(i, 1)
b(n, 2) = a(i, ii + iii)
Next
End If
Next
End If
Next
With Sheets("Sheet6").Cells(1).Resize(, 2)
.Value = [{"Item","Color"}]
.Offset(1).Resize(n).Value = b
End With
End Sub
Display More