Macro to convert an indented hierarchy to a database format
Thanks very much to JNevill for his solution - it is indeed significantly faster than my original code. I had to make some changes to accommodate more than one # column, as well as headers to the left of the indented hierarchy column i.e.:

Region        Base Level        Account 1     Account 2    

 USA         Level 1               500           800
 USA             Level 2a          300           400
 USA             Level 2b          200           400


Here is my new code based on JNevill's framework:

Sub HierarchyConvert()


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim WS                      As Worksheet
Dim SR                      As Range
Dim LastRow                 As Long
Dim rngReadCell             As Range
Dim rngWriteRow             As Range
Dim Indent                  As Integer
Dim LastIndent              As Integer
Dim MaxIndent               As Integer
Dim ValueArray(0 To 19)     As Variant

Set WS = ActiveWorkbook.ActiveSheet
Set SR = WS.Range(StartCell())
LastRow = SR.End(xlDown).Row
MaxIndent = 5

Set rngWriteRow = WS.Rows(SR.Row)

For x = 0 To 4
    SR.Offset(0, 1).EntireColumn.Insert
    SR.Offset(-1, 1) = "Level " &amp; 7 - x
Next x

SR.Offset(-1, 0) = "Level 2"
SR.Offset(-1, 5) = "PC"

For Each rngReadCell In WS.Range(SR.Address &amp; ":B" &amp; LastRow)
    Indent = rngReadCell.IndentLevel
    If Indent &lt;= LastIndent And LastIndent &lt;&gt; 0 Then
        Set rngWriteRow = rngWriteRow.Offset(1)
        For i = 1 To Indent
            rngWriteRow.Cells(1, i + 1).Value = rngWriteRow.Cells(1, i + 1).Offset(-1).Value
        Next i
    End If
    rngWriteRow.Cells(Indent + 2).Value = Trim(rngReadCell.Value)
    If Indent = MaxIndent Then
        'Copies leftmost header from base-level row to top left of write-row
        rngWriteRow.Cells(1) = rngReadCell.Offset(, -1).Value
        'Copies data to right of base-level row to the write-row
        For Z = 0 To 19
            ValueArray(Z) = rngReadCell.Offset(, Z + 6).Value
        Next Z
        For M = 0 To 19
            rngWriteRow.Cells(Indent + M + 3).Value = ValueArray(M)
        Next M
    End If
    LastIndent = Indent
Next rngReadCell

Range("A" &amp; SR.Offset(0, 1).End(xlDown).Row + 1 &amp; ":Z" &amp; LastRow + 1).ClearContents
WS.UsedRange.IndentLevel = 0

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub