VBA to unpivot a Fixed number of columns just like Power QUery

boydespasito · Oct 30th 2020

Hi dears!

my first time posting here and also fairly new to macro coding. Found out this Forum is very helpful for beginners like me so i am very excited to be helped by you guys!

i've been searching high and low about this code that i need to do but sadly , although some are close, it doesn't actually do what i need which is in fact just the very simple scenario.

I have 130 columns for example, and 13 of those columns has to be Unpivoted. That simple.

Number of rows may of course vary from time to time.

This works perfectly fine with Power Query but silly of me, vba doesn't do power query. lol

i have attached a sample file which may show how my data is being presented,

where columns A to E are the fixed array, and F onwards will be the columns subject for unpivotting.

Hope someone can really help me on this as i feel like my hairs are loosing now. lol.

Thank you so much in advance!

Let me know how i can repay your kindness.

**alansidman** · Oct 31st 2020

Quicker and easier in PQ, but here is some code for you.

Code

Option Explicit


Sub PQSubstitute()
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
Dim lr As Long, lr2 As Long
Dim i As Long
Application.ScreenUpdating = False
lr = s1.Range("A" & Rows.Count).End(xlUp).Row
s1.Range("A1:E1").Copy s2.Range("B1")


For i = 2 To lr
lr2 = s2.Range("G" & Rows.Count).End(xlUp).Row
s1.Range("F1:P1").Copy
s2.Range("A" & lr2 + 1).PasteSpecial xlPasteValues, , , True
s1.Range("A" & i & ":E" & i).Copy
s2.Range("B" & lr2 + 1).PasteSpecial xlPasteValues
s1.Range("F" & i & ":P" & i).Copy
s2.Range("G" & lr2 + 1).PasteSpecial xlPasteValues, , , True
Next i
lr2 = s2.Range("G" & Rows.Count).End(xlUp).Row
For i = 3 To lr
If s2.Range("B" & i) = "" Then
s2.Range("B" & i - 1 & ":F" & i - 1).Copy s2.Range("B" & i)
End If
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "complete"
End Sub

Display More

VBA to unpivot a Fixed number of columns just like Power QUery

Participate now!

Share

Tags