VBA Excel - Permutations based from two columns

Question

I'm looking for a way in VBA to realize a permutation based from two columns. I tried with Ubound but it failed What is the better way to write an efficient code for a large file ?

The default DATA is like that

And the final result that I'm looking for :

VBasic2008 · Accepted Answer · 2021-08-18 14:01:37Z

1

A Simple Partial Unpivot

The main non-VBA tool to do this is PowerQuery (formerly Get & Transform).

Option Explicit

Sub UnPivot()
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim srg As Range: Set srg = ws.Range("A1").CurrentRegion
    Dim rCount As Long: rCount = srg.Rows.Count
    If rCount < 2 Then Exit Sub
    
    Dim Data As Variant: Data = srg.Value
    
    Dim r As Long
    Dim c As Long
    For r = 2 To rCount
        For c = 4 To 2 Step -1
            Data(r, c) = Data(r, c - 1)
        Next c
        Data(r, 1) = Data(1, 3)
    Next r
    
    Data(1, 1) = "DATE"
    Data(1, 2) = "TITLE"
    Data(1, 3) = "KPI"
    Data(1, 4) = "VALUE"
    
    Dim drg As Range: Set drg = srg.Offset(, srg.Columns.Count + 1)
    drg.Value = Data
    
End Sub

answered Aug 18, 2021 at 14:01

VBasic2008

56.8k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Excel - Permutations based from two columns

1 Answer 1

A Simple Partial Unpivot

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

A Simple Partial Unpivot

Comments

Your Answer

Sign up or log in

Post as a guest

Related