Create Dynamic Range TransFormation with Excel VBA

Objective:

The goal of this VBA script is to:

1. Identify a dynamic range in an Excel sheet (i.e., a range with a varying number of rows and columns).
2. Transform the data structure by copying, reshaping, and outputting it in a new format.
3. Automate the process for real-world applications such as data consolidation, formatting, and reorganization.

VBA Code for Dynamic Range Transformation

Sub TransformDynamicRange()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim srcRange As Range, destRange As Range
    Dim destRow As Long, destCol As Long
    Dim r As Long, c As Long
    Dim newValue As Variant   
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change as needed   
    ' Identify the last row with data
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    ' Identify the last column with data
    lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column  
    ' Define the dynamic source range
    Set srcRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))  
    ' Define the output range (starting point)
    Set destRange = ws.Range("G1") ' Change as needed
    destRow = destRange.Row
    destCol = destRange.Column   
    ' Loop through the source range and transform data
    For r = 1 To lastRow
        For c = 1 To lastCol
            ' Get the value from the source range
            newValue = srcRange.Cells(r, c).Value           
            ' If value is not empty, write it to the new location
            If newValue <> "" Then
                ws.Cells(destRow, destCol).Value = newValue
                destRow = destRow + 1 ' Move to next row in output
            End If
        Next c
    Next r   
    ' Cleanup
    Set srcRange = Nothing
    Set destRange = Nothing
    Set ws = Nothing
    MsgBox "Dynamic Range Transformation Completed!", vbInformation
End Sub

Detailed Explanation of the Code

Step 1: Define the Worksheet

Set ws = ThisWorkbook.Sheets(« Sheet1 »)

• The code assigns Sheet1 as the active worksheet.
• Modify « Sheet1 » to match your sheet name.

Step 2: Identify the Dynamic Range

lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column

• The lastRow is determined by finding the last occupied row in column A.
• The lastCol is found by checking the last used column in row 1.
• This helps in defining a dynamic range rather than a fixed one.

Step 3: Set the Source and Destination Ranges

Set srcRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))

Set destRange = ws.Range(« G1 ») ‘ Change as needed

• srcRange holds the dynamic data range from (1,1) to (lastRow, lastCol).
• destRange starts at cell G1, where transformed data will be stored.

Step 4: Loop Through the Source Data

For r = 1 To lastRow

    For c = 1 To lastCol

        newValue = srcRange.Cells(r, c).Value

• Two nested loops iterate through each cell in the source range.
• newValue temporarily holds the data from the source cell.

Step 5: Store Transformed Data

If newValue <> «  » Then

    ws.Cells(destRow, destCol).Value = newValue

    destRow = destRow + 1 ‘ Move to next row in output

End If

• If a cell is not empty, it is copied to the destination range.
• The destRow increments to store data vertically in column G.

Step 6: Cleanup and Completion

Set srcRange = Nothing

Set destRange = Nothing

Set ws = Nothing

MsgBox « Dynamic Range Transformation Completed! », vbInformation

• Objects are set to Nothing to free up memory.
• A message box confirms the successful transformation.

How the Data is Transformed

Example Input Table (A1:C4)

Transformed Output (Column G)

• The data shifts from a table format to a single-column list.

Enhancements & Customization

1. Transform Data Horizontally
   • Instead of writing downwards, use destCol = destCol + 1 for horizontal output.
2. Filter Specific Data
   • Modify If newValue <> «  » to check conditions (If newValue > 50 Then).
3. Handle Large Data Sets Efficiently
   • Use arrays instead of direct cell access for improved performance.

Final Thoughts

This VBA macro is powerful for data transformations where:

• You need to reshape a dataset dynamically.
• Data is updated frequently, requiring automation.
• You want to prepare structured data for reports.