Uploaded September 2020 | Updated September 2026, 2 weeks ago

---

How to unstack multiple columns of data using VBA.
Here's the complete VBA code:
Sub DataFormatting()

Dim lastrow As Long, i As Long, k As Long, ponum As Long, totvalue As Long

Dim MaterialCode As String
k = 6
totvalue = 0
Dim nextblankrow As Long
nextblankrow = Sheet1.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0).Row
lastrow = Application.WorksheetFunction.CountA(Range("A:A"))


MaterialCode = InputBox("Enter Material code", "Material Code")
Range("F1") = "Material Code"
Range("G1") = "PO No"

For i = 2 To lastrow
If Cells(i, 1) = MaterialCode Then
Cells(nextblankrow, 6) = MaterialCode
Cells(nextblankrow, k + 1) = Cells(i, 2)
totvalue = totvalue + Cells(i, 3)
k = k + 1

End If
Next i
Cells(1, k + 1) = "Value"
Cells(nextblankrow, k + 1) = totvalue

End Sub