excel - VBA Excel - 基于两列的排列

Question

我正在寻找一种在 VBA 中实现基于两列的排列的方法。我尝试使用 Ubound 但失败了 为大文件编写高效代码的更好方法是什么？

默认的DATA是这样的

我正在寻找的最终结果：

标签： excelvbapermutationunpivot

Answer 1

一个简单的部分反透视

• 执行此操作的主要非 VBA 工具是PowerQuery（以前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