Create Dynamic Range Importing with Excel VBA

Objective

The goal is to create a dynamic range importing solution using VBA. The script will:

1. Identify the last row and last column dynamically.
2. Select and import the data into another worksheet.
3. Handle variable data sizes efficiently.

VBA Code: Create Dynamic Range Importing

Let’s assume we have data in « Sheet1 » that needs to be imported into « Sheet2 » dynamically.

Sub ImportDynamicRange()
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim rng As Range, destCell As Range
    ' Set references to worksheets
    Set wsSource = ThisWorkbook.Sheets("Sheet1")
    Set wsDest = ThisWorkbook.Sheets("Sheet2")
    ' Find the last used row in Sheet1 (assuming data starts from row 1)
    lastRow = wsSource.Cells(Rows.Count, 1).End(xlUp).Row
    ' Find the last used column in Sheet1 (assuming data starts from column A)
    lastCol = wsSource.Cells(1, Columns.Count).End(xlToLeft).Column
    ' Define the dynamic range
    Set rng = wsSource.Range(wsSource.Cells(1, 1), wsSource.Cells(lastRow, lastCol))
    ' Define the destination cell in Sheet2 (starting from A1)
    Set destCell = wsDest.Cells(1, 1)
    ' Copy and paste values to avoid formatting issues
    rng.Copy
    destCell.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False  ' Clear clipboard
    ' Notify the user
    MsgBox "Data imported successfully!", vbInformation, "Import Complete"
End Sub

Detailed Explanation

1. Define Worksheets

Set wsSource = ThisWorkbook.Sheets(« Sheet1 »)

Set wsDest = ThisWorkbook.Sheets(« Sheet2 »)

• wsSource refers to the sheet containing the data to be imported.
• wsDest refers to the sheet where the data will be copied.

2. Find the Last Used Row

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

• This searches column A (1st column) from the bottom (Rows.Count) and moves up (xlUp) to find the last non-empty row.

3. Find the Last Used Column

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

• This searches row 1 (header row) from the last column (Columns.Count) and moves left (xlToLeft) to find the last used column.

4. Define the Dynamic Range

Set rng = wsSource.Range(wsSource.Cells(1, 1), wsSource.Cells(lastRow, lastCol))

• This creates a dynamic range starting from A1 to the last detected row and column.

5. Define the Destination Cell

Set destCell = wsDest.Cells(1, 1)

• The data will be pasted starting from A1 in « Sheet2 ».

6. Copy and Paste Data

rng.Copy

destCell.PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

• The range is copied from « Sheet1 » and pasted as values only in « Sheet2 » to avoid formatting issues.

7. User Notification

MsgBox « Data imported successfully! », vbInformation, « Import Complete »

• This message informs the user that the import was successful.

Additional Enhancements

• Copy Formatting:

• PasteSpecial Paste:=xlPasteFormats

• Paste Data + Column Widths:

• PasteSpecial Paste:=xlPasteColumnWidths

• Error Handling:

• On Error Resume Next
• ‘ Code logic here
• On Error GoTo 0