VBA & Macro Problems

How to Create an Excel VBA Macro for Dynamic Ranges and Multiple Data Blocks

WPS Content Manager Sep 25, 2026 871 views

Question details

The user needs to create an Excel VBA macro that can dynamically find the last row of data, process multiple eight-column blocks starting from column AK, apply filters, and append the extracted results to a target worksheet.

How to Create an Excel VBA Macro for Dynamic Ranges and Multiple Data Blocks

Product: Excel
Device & OS: not provided
Scenario: Automating the extraction and filtering of monthly reports where the number of rows and columns can vary, requiring an adaptable script instead of fixed ranges.
Observed behavior: Recorded macros use fixed ranges and excessive Select statements, which causes them to fail or behave incorrectly when data size changes in subsequent months.

Before you start

Ensure you have saved your workbook as a Macro-Enabled Workbook (.xlsm) and that the Developer tab is enabled in your Excel ribbon to access the VBA Editor.

Solution 1Recommended

Implement a VBA Loop with Dynamic Worksheet and Cell References

Replace fixed ranges and "Select" statements with a script that dynamically calculates the last row and loops through your data blocks.

Recorded macros often hardcode ranges (like 'AK1:AR100'), which breaks when your data grows. Using dynamic variables for the last row and last column ensures your macro adapts to any dataset size automatically.

By looping through your eight-column blocks (e.g., from column AK to ED), you can filter and append data efficiently without relying on sluggish Select or Activate operations.

Define worksheets and variables

Open the VBA Editor (Alt + F11), insert a new module, and declare your variables using 'Dim SourceSheet As Worksheet, TargetSheet As Worksheet, LastRow As Long, LastCol As Long'.

Set references and find the last row

Assign your source and target sheets to the variables. Find the dynamic end of your data using 'LastRow = SourceSheet.Cells(SourceSheet.Rows.Count, "AK").End(xlUp).Row'.

Loop through columns and apply filters

Create a 'For' loop stepping by 8 columns (e.g., For i = 37 To 134 Step 8). Inside the loop, define the specific eight-column range based on the index, and apply 'AutoFilter' based on your required criteria.

Copy and append to the target sheet

Use 'SpecialCells(xlCellTypeVisible).Copy' to copy only the filtered visible rows. Paste them into the target sheet by dynamically finding its next available row using 'TargetSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1'.

Implement a VBA Loop with Dynamic Worksheet and Cell References

Avoid Select Statements: Directly referencing ranges instead of using '.Select' or '.Activate' significantly speeds up your macro execution and prevents screen flickering.

Advanced Spreadsheet Automation

Run Excel VBA Macros Seamlessly in WPS Spreadsheets

WPS Office provides robust support for standard VBA and Excel macros, allowing you to automate repetitive tasks, handle dynamic ranges, and process large data blocks with ease.

1. Open your Macro-Enabled Workbook: Launch WPS Spreadsheets and open your .xlsm file containing the dynamic range macros.
2. Access the Developer tools: Navigate to the 'Developer' tab on the ribbon to access the Macro and VBA environment features.
3. Run or edit your macro: Click on 'Macros' to execute your dynamic range script, or open the 'Visual Basic' editor to modify the code directly.

Fully compatible with Microsoft Excel .xlsm and .xlsb formatsBuilt-in VBA editor for writing, editing, and debugging macrosFaster processing of large data blocks and dynamic rangesFree and lightweight spreadsheet alternative with a familiar UI

microsoft office alternative - wps office

Frequently Asked Questions

Why does my recorded Excel macro fail when new data is added?

Recorded macros hardcode the specific cell ranges (e.g., AK1:AR50) that were selected during the recording process. If your new monthly data exceeds row 50, the macro will ignore the new rows. You must replace fixed ranges with dynamic range calculations using VBA to fix this.

How do I find the last row of data in a specific column using VBA?

You can find the last row in column AK by using the following VBA code: LastRow = Cells(Rows.Count, "AK").End(xlUp).Row. This dynamically checks from the very bottom of the worksheet upwards to find the last non-empty cell.

What is the best way to loop through data blocks in Excel VBA?

Use a 'For...Next' loop with the 'Step' keyword. For example, 'For i = 37 To 134 Step 8' will loop through column indices starting from AK (column 37) to ED (column 134) in increments of 8, allowing you to process eight-column blocks one at a time.

Can I run Excel VBA macros in WPS Office?

Yes, WPS Office supports VBA macros. You can open your .xlsm files, access the Developer tab, and run or edit your VBA code just as you would in Microsoft Excel, provided you have the WPS VBA module installed and enabled.

Excel & Data Problems