Excel VBA, transformation of data crashes excel

jrwrita

Board Regular
Joined
May 7, 2015
Messages
206
I have a code that crashes my excel, it takes the data from one sheet and transforms it from a tabular view to vertical. I believe it is slow because it is taking it from a database view to over 45k rows.


does anyone have any tips for optimizing this code? I need to avoid writing cell by cell and copy and pasting. Crashes my excel.


Code:
    Sub Unpivot()
        Call ReversePivotTable("Sheet1", "A", "C", "Sheet2", "Name")
    End Sub






    Sub ReversePivotTable(source_sheet, from_col, to_col, target_sheet, Optional type_header = "type", Optional value_header = "value")
    
        Application.ScreenUpdating = False
        LAST_ROW = Sheets(source_sheet).Cells(Rows.count, 1).End(xlUp).Row
        If LAST_ROW > 1 Then
            Sheets(target_sheet).Cells.ClearContents
        Else
            Exit Sub
        End If
        
        pvt_type_col = Sheets(target_sheet).Range(to_col & 1).Offset(0, 1).column 'D
        pvt_value_col = Sheets(target_sheet).Range(to_col & 1).Offset(0, 2).column 'E
    
        'get headers
        Sheets(source_sheet).Range(from_col & ":" & to_col).copy
        Sheets(target_sheet).Range("A1").PasteSpecial xlPasteValues
        Sheets(target_sheet).Cells(1, pvt_type_col).Value = type_header
        Sheets(target_sheet).Cells(1, pvt_value_col).Value = value_header
        
        
    
        'tranform data
        curr_row = 2
        With Sheets(source_sheet)
            last_col = .Cells(1, Columns.count).End(xlToLeft).column
                For Each c In .Range("A2", .Range("A" & Rows.count).End(xlUp))
                    Set rng = .Range(.Cells(c.Row, pvt_type_col), .Cells(c.Row, last_col))
                    numbers = Application.WorksheetFunction.CountIf(rng, "<>""")
                    If numbers > 0 Then
                        Sheets(source_sheet).Range(from_col & c.Row & ":" & to_col & c.Row).copy
                        Sheets(target_sheet).Range(from_col & curr_row & ":" & from_col & curr_row + numbers - 1).PasteSpecial Paste:=xlPasteValues
                        Application.CutCopyMode = False
                        b = curr_row
                        For a = pvt_type_col To last_col Step 1
                            If IsNumeric(.Cells(c.Row, a).Value) Then
                            'If .Cells(c.Row, a).Value <> "" Then
                                Sheets(target_sheet).Cells(b, pvt_type_col) = .Cells(1, a)
                                Sheets(target_sheet).Cells(b, pvt_value_col) = .Cells(c.Row, a)
                                b = b + 1
                            End If
                        Next a
                        curr_row = curr_row + numbers
                        If curr_row Mod 10 = 0 Then DoEvents
                    End If
                Next c
        End With
        Sheets(target_sheet).Select
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    
    End Sub
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
How is the table laid out to start? It would probably be easier to suggest how to write the macro rather than how to edit this one
 
Upvote 0
Data example:
Code:
+---------+------------------+-----------------+--------------+-------------+--------------+-------------+--------------+--------------+-------------+-------------+-------------+--------------+--------------+-------------+-------------+--------+--------+--------+--------------+--------------+-------------+--------------+-------------+-------------+-------------+-------------+--------------+-------------+--------------+-------------+--------------+-------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
|  col 1  |      col 2       |      col 3      |    col 4     |    col 5    |    col 6     |    col 7    |    col 8     |    col 9     |   col 10    |   col 11    |   col 12    |    col 13    |    col 14    |   col 15    |   col 16    | col 17 | col 18 | col 19 |    col 20    |    col 21    |   col 22    |    col 23    |   col 24    |   col 25    |   col 26    |   col 27    |    col 28    |   col 29    |    col 30    |   col 31    |    col 32    |   col 33    | col 34 | col 35 | col 36 | col 37 | col 38 | col 39 | col 40 | col 41 | col 42 | col 43 | col 44 | col 45 | col 46 | col 47 | col 48 | col 49 |
+---------+------------------+-----------------+--------------+-------------+--------------+-------------+--------------+--------------+-------------+-------------+-------------+--------------+--------------+-------------+-------------+--------+--------+--------+--------------+--------------+-------------+--------------+-------------+-------------+-------------+-------------+--------------+-------------+--------------+-------------+--------------+-------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| mrexcel | questions forums | excel questions | -540.0689323 | 1543.570725 | -144.7954348 | 2298.261951 | -9019.970702 | -14669.27805 |  2400.31011 | 642.2459256 | 5573.176935 | -19167.60096 | -17070.78503 | 2884.343252 |   2262.2904 |      0 |      0 |      0 | -4866.524221 | -5470.616311 | 6722.889306 | -6749.153327 | 8483.707603 | 7513.052842 | 3768.659869 | 8600.703543 | -8642.799155 | 1322.251923 | -1323.911031 | 3651.739593 | -259.3401823 | 9369.890794 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |
| mrexcel | questions forums | excel questions | -325.5117945 | 641.8568521 | -58.21010305 | 977.4626836 | -3505.695779 | -7455.410001 | 777.9341271 | 385.2714806 | 1932.531773 | -8861.136183 | -6679.463121 | 1177.775583 | 881.2548725 |      0 |      0 |      0 | -1813.822794 | -2266.860562 | 2278.669772 | -2361.758467 | 3356.446385 | 2741.992369 | 1461.950204 | 3289.154294 |  -3469.10217 | 804.7989704 | -816.9003551 | 1907.515323 |  432.8435868 | 3074.256129 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |
| mrexcel | questions forums | excel questions | -36.42618332 | 65.26139258 | -6.513963305 | 99.38442773 | -435.0485137 | -1047.099199 | 79.09717611 | 39.17283622 | 186.7060257 | -1272.372107 |  -922.750792 | 118.3261869 | 89.60240903 |      0 |      0 |      0 | -210.3183182 | -267.1376584 | 214.6223869 | -280.0000537 | 293.4738136 | 248.5196226 | 144.0720039 | 288.5506437 | -430.0886416 | 81.82868405 | -91.41469707 | 184.4395708 |  44.00977438 | 272.8284368 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |
| mrexcel | questions forums | excel questions | -582.3647427 | 1316.573479 | -165.4555206 | 1925.519573 | -7138.977944 | -17532.94829 | 1404.004642 | 930.6126154 | 3648.013625 | -19585.55834 |  -13758.8035 | 2376.319408 |   1898.9449 |      0 |      0 |      0 | -3625.886962 | -4833.808881 | 4232.764078 | -4449.956081 | 6883.584715 |  5398.12044 | 4048.773452 | 6632.405148 | -7240.871663 | 1959.676076 | -2008.657583 | 4413.431721 |  1360.661107 | 5484.849776 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |
+---------+------------------+-----------------+--------------+-------------+--------------+-------------+--------------+--------------+-------------+-------------+-------------+--------------+--------------+-------------+-------------+--------+--------+--------+--------------+--------------+-------------+--------------+-------------+-------------+-------------+-------------+--------------+-------------+--------------+-------------+--------------+-------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+

Expected to be pivoted on col 3 and col D would go to a vertical look.

Code:
+---------+------------------+-----------------+-----------+--------------+
|  col 1  |      col 2       |      col 3      | Attribute |    Value     |
+---------+------------------+-----------------+-----------+--------------+
| mrexcel | questions forums | excel questions | col 4     | -540.0689323 |
| mrexcel | questions forums | excel questions | col 5     |  1543.570725 |
| mrexcel | questions forums | excel questions | col 6     | -144.7954348 |
| mrexcel | questions forums | excel questions | col 7     |  2298.261951 |
| mrexcel | questions forums | excel questions | col 8     | -9019.970702 |
| mrexcel | questions forums | excel questions | col 9     | -14669.27805 |
| mrexcel | questions forums | excel questions | col 10    |   2400.31011 |
| mrexcel | questions forums | excel questions | col 11    |  642.2459256 |
| mrexcel | questions forums | excel questions | col 12    |  5573.176935 |
| mrexcel | questions forums | excel questions | col 13    | -19167.60096 |
| mrexcel | questions forums | excel questions | col 14    | -17070.78503 |
| mrexcel | questions forums | excel questions | col 15    |  2884.343252 |
| mrexcel | questions forums | excel questions | col 16    |    2262.2904 |
| mrexcel | questions forums | excel questions | col 17    |            0 |
| mrexcel | questions forums | excel questions | col 18    |            0 |
| mrexcel | questions forums | excel questions | col 19    |            0 |
| mrexcel | questions forums | excel questions | col 20    | -4866.524221 |
| mrexcel | questions forums | excel questions | col 21    | -5470.616311 |
| mrexcel | questions forums | excel questions | col 22    |  6722.889306 |
| mrexcel | questions forums | excel questions | col 23    | -6749.153327 |
| mrexcel | questions forums | excel questions | col 24    |  8483.707603 |
| mrexcel | questions forums | excel questions | col 25    |  7513.052842 |
| mrexcel | questions forums | excel questions | col 26    |  3768.659869 |
| mrexcel | questions forums | excel questions | col 27    |  8600.703543 |
| mrexcel | questions forums | excel questions | col 28    | -8642.799155 |
| mrexcel | questions forums | excel questions | col 29    |  1322.251923 |
| mrexcel | questions forums | excel questions | col 30    | -1323.911031 |
| mrexcel | questions forums | excel questions | col 31    |  3651.739593 |
| mrexcel | questions forums | excel questions | col 32    | -259.3401823 |
| mrexcel | questions forums | excel questions | col 33    |  9369.890794 |
| mrexcel | questions forums | excel questions | col 34    |            0 |
| mrexcel | questions forums | excel questions | col 35    |            0 |
| mrexcel | questions forums | excel questions | col 36    |            0 |
| mrexcel | questions forums | excel questions | col 37    |            0 |
| mrexcel | questions forums | excel questions | col 38    |            0 |
| mrexcel | questions forums | excel questions | col 39    |            0 |
| mrexcel | questions forums | excel questions | col 40    |            0 |
| mrexcel | questions forums | excel questions | col 41    |            0 |
| mrexcel | questions forums | excel questions | col 42    |            0 |
| mrexcel | questions forums | excel questions | col 43    |            0 |
| mrexcel | questions forums | excel questions | col 44    |            0 |
| mrexcel | questions forums | excel questions | col 45    |            0 |
| mrexcel | questions forums | excel questions | col 46    |            0 |
| mrexcel | questions forums | excel questions | col 47    |            0 |
| mrexcel | questions forums | excel questions | col 48    |            0 |
| mrexcel | questions forums | excel questions | col 49    |            0 |
| mrexcel | questions forums | excel questions | col 4     | -325.5117945 |
| mrexcel | questions forums | excel questions | col 5     |  641.8568521 |
| mrexcel | questions forums | excel questions | col 6     | -58.21010305 |
| mrexcel | questions forums | excel questions | col 7     |  977.4626836 |
| mrexcel | questions forums | excel questions | col 8     | -3505.695779 |
| mrexcel | questions forums | excel questions | col 9     | -7455.410001 |
| mrexcel | questions forums | excel questions | col 10    |  777.9341271 |
| mrexcel | questions forums | excel questions | col 11    |  385.2714806 |
| mrexcel | questions forums | excel questions | col 12    |  1932.531773 |
| mrexcel | questions forums | excel questions | col 13    | -8861.136183 |
| mrexcel | questions forums | excel questions | col 14    | -6679.463121 |
| mrexcel | questions forums | excel questions | col 15    |  1177.775583 |
| mrexcel | questions forums | excel questions | col 16    |  881.2548725 |
| mrexcel | questions forums | excel questions | col 17    |            0 |
| mrexcel | questions forums | excel questions | col 18    |            0 |
| mrexcel | questions forums | excel questions | col 19    |            0 |
| mrexcel | questions forums | excel questions | col 20    | -1813.822794 |
| mrexcel | questions forums | excel questions | col 21    | -2266.860562 |
| mrexcel | questions forums | excel questions | col 22    |  2278.669772 |
| mrexcel | questions forums | excel questions | col 23    | -2361.758467 |
| mrexcel | questions forums | excel questions | col 24    |  3356.446385 |
| mrexcel | questions forums | excel questions | col 25    |  2741.992369 |
| mrexcel | questions forums | excel questions | col 26    |  1461.950204 |
| mrexcel | questions forums | excel questions | col 27    |  3289.154294 |
| mrexcel | questions forums | excel questions | col 28    |  -3469.10217 |
| mrexcel | questions forums | excel questions | col 29    |  804.7989704 |
| mrexcel | questions forums | excel questions | col 30    | -816.9003551 |
| mrexcel | questions forums | excel questions | col 31    |  1907.515323 |
| mrexcel | questions forums | excel questions | col 32    |  432.8435868 |
| mrexcel | questions forums | excel questions | col 33    |  3074.256129 |
| mrexcel | questions forums | excel questions | col 34    |            0 |
| mrexcel | questions forums | excel questions | col 35    |            0 |
| mrexcel | questions forums | excel questions | col 36    |            0 |
| mrexcel | questions forums | excel questions | col 37    |            0 |
| mrexcel | questions forums | excel questions | col 38    |            0 |
| mrexcel | questions forums | excel questions | col 39    |            0 |
| mrexcel | questions forums | excel questions | col 40    |            0 |
| mrexcel | questions forums | excel questions | col 41    |            0 |
| mrexcel | questions forums | excel questions | col 42    |            0 |
| mrexcel | questions forums | excel questions | col 43    |            0 |
| mrexcel | questions forums | excel questions | col 44    |            0 |
| mrexcel | questions forums | excel questions | col 45    |            0 |
| mrexcel | questions forums | excel questions | col 46    |            0 |
| mrexcel | questions forums | excel questions | col 47    |            0 |
| mrexcel | questions forums | excel questions | col 48    |            0 |
| mrexcel | questions forums | excel questions | col 49    |            0 |
| mrexcel | questions forums | excel questions | col 4     | -36.42618332 |
| mrexcel | questions forums | excel questions | col 5     |  65.26139258 |
| mrexcel | questions forums | excel questions | col 6     | -6.513963305 |
| mrexcel | questions forums | excel questions | col 7     |  99.38442773 |
| mrexcel | questions forums | excel questions | col 8     | -435.0485137 |
| mrexcel | questions forums | excel questions | col 9     | -1047.099199 |
| mrexcel | questions forums | excel questions | col 10    |  79.09717611 |
| mrexcel | questions forums | excel questions | col 11    |  39.17283622 |
| mrexcel | questions forums | excel questions | col 12    |  186.7060257 |
| mrexcel | questions forums | excel questions | col 13    | -1272.372107 |
| mrexcel | questions forums | excel questions | col 14    |  -922.750792 |
| mrexcel | questions forums | excel questions | col 15    |  118.3261869 |
| mrexcel | questions forums | excel questions | col 16    |  89.60240903 |
| mrexcel | questions forums | excel questions | col 17    |            0 |
| mrexcel | questions forums | excel questions | col 18    |            0 |
| mrexcel | questions forums | excel questions | col 19    |            0 |
| mrexcel | questions forums | excel questions | col 20    | -210.3183182 |
| mrexcel | questions forums | excel questions | col 21    | -267.1376584 |
| mrexcel | questions forums | excel questions | col 22    |  214.6223869 |
| mrexcel | questions forums | excel questions | col 23    | -280.0000537 |
| mrexcel | questions forums | excel questions | col 24    |  293.4738136 |
| mrexcel | questions forums | excel questions | col 25    |  248.5196226 |
| mrexcel | questions forums | excel questions | col 26    |  144.0720039 |
| mrexcel | questions forums | excel questions | col 27    |  288.5506437 |
| mrexcel | questions forums | excel questions | col 28    | -430.0886416 |
| mrexcel | questions forums | excel questions | col 29    |  81.82868405 |
| mrexcel | questions forums | excel questions | col 30    | -91.41469707 |
| mrexcel | questions forums | excel questions | col 31    |  184.4395708 |
| mrexcel | questions forums | excel questions | col 32    |  44.00977438 |
| mrexcel | questions forums | excel questions | col 33    |  272.8284368 |
| mrexcel | questions forums | excel questions | col 34    |            0 |
| mrexcel | questions forums | excel questions | col 35    |            0 |
| mrexcel | questions forums | excel questions | col 36    |            0 |
| mrexcel | questions forums | excel questions | col 37    |            0 |
| mrexcel | questions forums | excel questions | col 38    |            0 |
| mrexcel | questions forums | excel questions | col 39    |            0 |
| mrexcel | questions forums | excel questions | col 40    |            0 |
| mrexcel | questions forums | excel questions | col 41    |            0 |
| mrexcel | questions forums | excel questions | col 42    |            0 |
| mrexcel | questions forums | excel questions | col 43    |            0 |
| mrexcel | questions forums | excel questions | col 44    |            0 |
| mrexcel | questions forums | excel questions | col 45    |            0 |
| mrexcel | questions forums | excel questions | col 46    |            0 |
| mrexcel | questions forums | excel questions | col 47    |            0 |
| mrexcel | questions forums | excel questions | col 48    |            0 |
| mrexcel | questions forums | excel questions | col 49    |            0 |
| mrexcel | questions forums | excel questions | col 4     | -582.3647427 |
| mrexcel | questions forums | excel questions | col 5     |  1316.573479 |
| mrexcel | questions forums | excel questions | col 6     | -165.4555206 |
| mrexcel | questions forums | excel questions | col 7     |  1925.519573 |
| mrexcel | questions forums | excel questions | col 8     | -7138.977944 |
| mrexcel | questions forums | excel questions | col 9     | -17532.94829 |
| mrexcel | questions forums | excel questions | col 10    |  1404.004642 |
| mrexcel | questions forums | excel questions | col 11    |  930.6126154 |
| mrexcel | questions forums | excel questions | col 12    |  3648.013625 |
| mrexcel | questions forums | excel questions | col 13    | -19585.55834 |
| mrexcel | questions forums | excel questions | col 14    |  -13758.8035 |
| mrexcel | questions forums | excel questions | col 15    |  2376.319408 |
| mrexcel | questions forums | excel questions | col 16    |    1898.9449 |
| mrexcel | questions forums | excel questions | col 17    |            0 |
| mrexcel | questions forums | excel questions | col 18    |            0 |
| mrexcel | questions forums | excel questions | col 19    |            0 |
| mrexcel | questions forums | excel questions | col 20    | -3625.886962 |
| mrexcel | questions forums | excel questions | col 21    | -4833.808881 |
| mrexcel | questions forums | excel questions | col 22    |  4232.764078 |
| mrexcel | questions forums | excel questions | col 23    | -4449.956081 |
| mrexcel | questions forums | excel questions | col 24    |  6883.584715 |
| mrexcel | questions forums | excel questions | col 25    |   5398.12044 |
| mrexcel | questions forums | excel questions | col 26    |  4048.773452 |
| mrexcel | questions forums | excel questions | col 27    |  6632.405148 |
| mrexcel | questions forums | excel questions | col 28    | -7240.871663 |
| mrexcel | questions forums | excel questions | col 29    |  1959.676076 |
| mrexcel | questions forums | excel questions | col 30    | -2008.657583 |
| mrexcel | questions forums | excel questions | col 31    |  4413.431721 |
| mrexcel | questions forums | excel questions | col 32    |  1360.661107 |
| mrexcel | questions forums | excel questions | col 33    |  5484.849776 |
| mrexcel | questions forums | excel questions | col 34    |            0 |
| mrexcel | questions forums | excel questions | col 35    |            0 |
| mrexcel | questions forums | excel questions | col 36    |            0 |
| mrexcel | questions forums | excel questions | col 37    |            0 |
| mrexcel | questions forums | excel questions | col 38    |            0 |
| mrexcel | questions forums | excel questions | col 39    |            0 |
| mrexcel | questions forums | excel questions | col 40    |            0 |
| mrexcel | questions forums | excel questions | col 41    |            0 |
| mrexcel | questions forums | excel questions | col 42    |            0 |
| mrexcel | questions forums | excel questions | col 43    |            0 |
| mrexcel | questions forums | excel questions | col 44    |            0 |
| mrexcel | questions forums | excel questions | col 45    |            0 |
| mrexcel | questions forums | excel questions | col 46    |            0 |
| mrexcel | questions forums | excel questions | col 47    |            0 |
| mrexcel | questions forums | excel questions | col 48    |            0 |
| mrexcel | questions forums | excel questions | col 49    |            0 |
+---------+------------------+-----------------+-----------+--------------+

The problem is also that is it much more data then just this.
 
Upvote 0
Are you populating the worksheet one cell at a time? That is notoriously slow.

Get your data (range A1:XX9999 or whatever) into a VBA array in one step:

Code:
Dim vInput As Variant
vInput = Sheets(source_sheet).Range("A1:XX9999").Value2

(.Value2 is like .Value, but possibly a bit faster.)

Then define a new array in VBA, dimension it, and use the same logic to populate it:

Code:
Dim vOutput As Variant
ReDim vOutput(1 to nRows, 1 to nCols)

' blah blah

vOutput(iRow, iCol) = whatever

Finally, put this array into the sheet in one step:

Code:
Sheets(target_sheet).Range("A1").resize(nRows, nCols).Value2 = vOutput
 
Upvote 0
This code transformed 4536 row x 104 columns to 458136 rows x 5 columns in 8 seconds.

Code:
Option Explicit
Sub transformData()
    Dim lastRow As Long, lastColumn As Long, r As Long, c As Long, i As Long
    Dim outRow As Long, blockLength As Long, outRowEndOfBlock As Long
    Dim shtIn As Worksheet, shtOut As Worksheet
    Dim inData() As Variant, key() As Variant, outData() As Variant
    Dim attributes() As Variant, attributesTransposed As Variant
    
    Set shtIn = ThisWorkbook.Worksheets("Source")   '<--- enter your sheetlabel here
    Set shtOut = ThisWorkbook.Worksheets("sink")    '<--- enter your sheetlabel here
    
    With shtIn
        lastRow = .Cells(1, 1).End(xlDown).Row
        lastColumn = .Cells(1, 1).End(xlToRight).Column
        blockLength = lastColumn - 3
        
        attributes = .Range(.Cells(1, 4), .Cells(1, lastColumn))
        attributesTransposed = WorksheetFunction.Transpose(attributes)
        
        outRow = 2
        ReDim outKeys(1 To blockLength, 1 To 3)
        With shtOut
            .Range("A2", .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, lastColumn)).Clear
        End With
        
        For r = 2 To lastRow
            outRowEndOfBlock = outRow + blockLength - 1
            
            key = .Range(.Cells(r, 1), .Cells(r, 3))    'columns A:C
            
            For i = 1 To blockLength        'repeat A:C in array
                outKeys(i, 1) = key(1, 1)
                outKeys(i, 2) = key(1, 2)
                outKeys(i, 3) = key(1, 3)
            Next i
            
            '--- repeated A:C columns to output sheet ---
            shtOut.Range(shtOut.Cells(outRow, 1), _
                         shtOut.Cells(outRowEndOfBlock, 3)) = _
            outKeys
            
            '--- transposed attributes  to output sheet ---
            shtOut.Range(shtOut.Cells(outRow, 4), _
                         shtOut.Cells(outRowEndOfBlock, 4)) = _
            attributesTransposed
            
            '--- transposed data to output sheet ---
            inData = .Range(.Cells(r, 4), .Cells(r, lastColumn))
            outData = WorksheetFunction.Transpose(inData)
            
            shtOut.Range(shtOut.Cells(outRow, 5), _
                         shtOut.Cells(outRowEndOfBlock, 5)) = _
            outData
            
            '--- point to row following block ---
            outRow = outRowEndOfBlock + 1
            
        Next r
    End With
End Sub
 
Upvote 0
This code transformed 4536 row x 104 columns to 458136 rows x 5 columns in 8 seconds.

Code:
Option Explicit
Sub transformData()
    Dim lastRow As Long, lastColumn As Long, r As Long, c As Long, i As Long
    Dim outRow As Long, blockLength As Long, outRowEndOfBlock As Long
    Dim shtIn As Worksheet, shtOut As Worksheet
    Dim inData() As Variant, key() As Variant, outData() As Variant
    Dim attributes() As Variant, attributesTransposed As Variant
    
    Set shtIn = ThisWorkbook.Worksheets("Source")   '<--- enter your sheetlabel here
    Set shtOut = ThisWorkbook.Worksheets("sink")    '<--- enter your sheetlabel here
    
    With shtIn
        lastRow = .Cells(1, 1).End(xlDown).Row
        lastColumn = .Cells(1, 1).End(xlToRight).Column
        blockLength = lastColumn - 3
        
        attributes = .Range(.Cells(1, 4), .Cells(1, lastColumn))
        attributesTransposed = WorksheetFunction.Transpose(attributes)
        
        outRow = 2
        ReDim outKeys(1 To blockLength, 1 To 3)
        With shtOut
            .Range("A2", .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, lastColumn)).Clear
        End With
        
        For r = 2 To lastRow
            outRowEndOfBlock = outRow + blockLength - 1
            
            key = .Range(.Cells(r, 1), .Cells(r, 3))    'columns A:C
            
            For i = 1 To blockLength        'repeat A:C in array
                outKeys(i, 1) = key(1, 1)
                outKeys(i, 2) = key(1, 2)
                outKeys(i, 3) = key(1, 3)
            Next i
            
            '--- repeated A:C columns to output sheet ---
            shtOut.Range(shtOut.Cells(outRow, 1), _
                         shtOut.Cells(outRowEndOfBlock, 3)) = _
            outKeys
            
            '--- transposed attributes  to output sheet ---
            shtOut.Range(shtOut.Cells(outRow, 4), _
                         shtOut.Cells(outRowEndOfBlock, 4)) = _
            attributesTransposed
            
            '--- transposed data to output sheet ---
            inData = .Range(.Cells(r, 4), .Cells(r, lastColumn))
            outData = WorksheetFunction.Transpose(inData)
            
            shtOut.Range(shtOut.Cells(outRow, 5), _
                         shtOut.Cells(outRowEndOfBlock, 5)) = _
            outData
            
            '--- point to row following block ---
            outRow = outRowEndOfBlock + 1
            
        Next r
    End With
End Sub

will this do what I have done above? Is all I need to do is put in my sheet names?
 
Upvote 0
Forgot to say that I am getting an error here: outData = WorksheetFunction.Transpose(inData)
 
Upvote 0
As much of a VBA fan as I am, I'm still going to tell you that you should use Power Query, or Get & Transform as it has been renamed.

First, select the data and press Ctrl+T to convert the data range into a Table. If you don't already use Tables, you should, because they are full of awesome magic.

tNiigBK.png


Select a cell in this table. On Excel's Data tab, find the Get & Transform Data group on the left, and click the From Table/Range button.

The Power Query editor will open. The first column is selected. Press Ctrl and click on the second and third column header to add these columns to the selection.

On the Transform tab, click the Unpivot Columns dropdown and select Unpivot Other Columns. That's all, the data is now converted.

On the Home tab, click Close & Load. Power Query inserts a new tab with the transformed data in a new table on this sheet.

lqD4Rj8.png


The great thing about Power Query is that you can easily reuse the query. Also the steps of the query are recorded in the query editor, so you can edit the individual steps.

Excel MVP John MacDougall has recently written a tutorial for my blog, Importing and Cleaning Data with Power Query, which steps through a slightly more complicated transformation than this one.
 
Upvote 0
Can I make the query into a vba code? That would also work, I want to make it automated so I dont have to click power query and do all the steps. I want as little steps as possible.
 
Upvote 0
Well, creating the query in the first place was very few steps, and took less time to do than to write about. You don't need to recreate the query.

Once the query has been run once in Excel using Power Query, you can refresh it (in case the data has changed) by right clicking on a cell in the output table and selecting Refresh.As simple as calling the VBA procedure.

The VBA procedure works like this. Select the cell and run this line of code:

Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

Alternatively, click on the Queries & Connections button, from the Queries & Connections group of the ribbon. Right click on the query in the task pane, and choose Refresh. In code:

ActiveWorkbook.Connections("Query - UnpivotTable").Refresh

(I have already named the query "UnpivotTable".)

If you want to run the query on different data, paste the new data into your original table, and rerun the query. The transformed data will appear in the same output table.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top