VBA & Macro Problems

How to Make Excel Macro Ranges Adjust Automatically to the Last Row

Kushani Nimanthika Oct 8, 2026 870 views

Question details

The user needs an Excel VBA macro to dynamically adjust formula autofill and sorting ranges based on the total number of records, rather than using fixed ranges that fail on files with different data sizes.

How to Make Excel Macro Ranges Adjust Automatically to the Last Row

Product: Microsoft Excel
Device & OS: not provided
Scenario: Running a VBA macro to copy formulas and sort data across workbooks that contain varying numbers of rows.
Observed behavior: The macro applies formulas or sorts data using a fixed range from a previous workbook, causing it to miss rows in larger files or include blank rows in smaller files.

Before you start

Before editing your VBA code, ensure you have saved a backup copy of your macro-enabled workbook and enable the Developer tab to access the Visual Basic Editor.

Solution 1Recommended

Calculate the Last Row Dynamically in VBA

Use VBA's built-in capabilities to find the last populated row in a specific column and apply it to your AutoFill or sorting destination range.

By replacing hardcoded ranges like 'A1:A100' with a dynamic variable that counts the rows, your macro will perfectly adapt to any dataset size.

Open the Visual Basic Editor

Press Alt + F11 on your keyboard to open the VBA Editor, then locate the module containing your macro.

Declare the Last Row variable

At the beginning of your macro, declare a variable to store the row number by typing: Dim LastRow As Long.

Calculate the last row

Assign the last used row to the variable using the End(xlUp) method. For example: LastRow = Cells(Rows.Count, "A").End(xlUp).Row (assuming column A always has data).

Update your AutoFill destination

Replace your fixed autofill range with the dynamic calculation: ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(LastRow, ActiveCell.Column)).

Update your sorting range

If you need to sort the data, apply the variable to the range string. For example, to sort columns A through AK: Range("A1:AK" & LastRow).Sort Key1:=Range("A1"), Order1:=xlAscending.

Calculate the Last Row Dynamically in VBA

Dynamic Range Applied: Your macro will now automatically scale the formula copying and sorting actions to match the exact number of data rows in any workbook.

Solution 2

Convert Data to an Excel Table

Formatting your dataset as a Table (ListObject) automatically expands formulas and ranges when new rows are added, which can reduce the need for complex VBA dynamic range code.

Advanced Macro Support

Write and Run VBA Macros Seamlessly in WPS Office

WPS Spreadsheet offers robust support for VBA and macros, allowing you to automate tasks and run your existing Excel scripts without modification. It perfectly handles dynamic ranges and automated sorting.

1. Download and Install: Download WPS Office for free from the official website and install it on your computer.
2. Open your Workbook: Launch WPS Spreadsheet and open your macro-enabled workbook (.xlsm or .xls).
3. Access the Developer Tab: Navigate to the Developer tab on the top ribbon. If prompted, enable macros for the current session.
4. Edit or Run the Macro: Click on 'Visual Basic' to edit your dynamic range scripts or click 'Macros' to execute the automation across varying file sizes instantly.

Fully compatible with Microsoft Excel VBA scripts (.xlsm, .xls).Easily automate dynamic ranges, formula autofills, and data sorting.Free, lightweight, and fast-loading spreadsheet editor.Familiar user interface with a built-in Developer tab for easy macro management.

microsoft office alternative - wps office

Frequently Asked Questions

How do I find the last column dynamically in VBA?

You can find the last used column using a similar method: LastCol = Cells(1, Columns.Count).End(xlToLeft).Column. This counts backward from the very last column in row 1 to find the last column containing data.

Why is my dynamic last row calculation returning 1?

This usually happens if the reference column (e.g., Column A) is completely empty. Ensure you are referencing a column that reliably contains data down to the very last row of your dataset.

Can I use the LastRow variable for defining print areas?

Yes. Once you have calculated the LastRow, you can set the print area dynamically by using code like: ActiveSheet.PageSetup.PrintArea = "$A$1:$F$" & LastRow.

Excel & Data Problems