How to Make an Excel VBA Macro Automatically Go to the Last Used Row
Question details
The user wants to modify an Excel VBA macro so it dynamically selects the last populated row instead of stopping at a previously recorded static range limit.
- Product
- Excel VBA / WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Running a recorded macro on a dataset that has expanded with new rows.
- Observed behavior
- The macro relies on a Ctrl+Shift+Down Arrow recording, which causes it to stop at the old end of the data series and ignore newly added rows.
Ensure you have the Developer tools enabled in your spreadsheet application and that your workbook is saved in a macro-enabled format (like .xlsm) before editing the VBA code.
Use the End(xlUp) Method to Dynamically Find the Last Row
Replace the static range selection in your recorded macro with the End(xlUp) command to ensure the macro always finds the true bottom of your dataset.
Recorded macros typically hardcode cell references or rely on keystrokes like Ctrl+Shift+Down. If empty cells exist in your data or new rows are added later, these methods fail to capture the full range. The End(xlUp) method solves this by starting from the very bottom of the worksheet and jumping up to the first non-empty cell, much like pressing Ctrl+Up Arrow from the last possible row.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the Project Explorer panel on the left, find the module containing your recorded macro and double-click to open it.
Look for the line of code that selects your data. It often ends with .End(xlDown).Select or specifies a fixed range like Range("B1:B50").Select.
Delete the static range selection and replace it with: ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Select
If your macro needs to run on a specific worksheet in the background, use the sheet name instead: Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Select
Automate Workflows with WPS Spreadsheets VBA
WPS Office offers robust support for VBA and Macros, allowing you to seamlessly run, record, and edit VBA scripts just like you do in Microsoft Excel.
- 1. Open Your Workbook: Open your macro-enabled workbook (.xlsm) in WPS Spreadsheets.
- 2. Access Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click on 'Visual Basic Editor'.
- 3. Edit Your Macro: Locate your macro module and paste or type your dynamic End(xlUp) VBA script to target the last row.
- 4. Run and Automate: Save your code and run the macro directly from the WPS interface to instantly process all your dynamic data rows.
Frequently Asked Questions
Why does my recorded macro stop before reaching the actual last row?
When you record a macro using the Ctrl+Shift+Down shortcut, it stops at the first empty cell it encounters. If your data has blank rows, or if you've added data beyond the originally recorded range limit, the macro will not capture the newly added rows dynamically.
Can I select the entire row after finding the last used cell?
Yes. You can chain the .EntireRow property to the method. For example, to select the entire last populated row based on column B, use: ActiveSheet.Cells(Rows.Count, "B").End(xlUp).EntireRow.Select.
How do I dynamically find the last used column instead of the last row?
To find the last used column, you can use the End(xlToLeft) method. It starts from the far-right edge of the worksheet and moves left to the first non-empty cell: ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Select.
