VBA & Macro Problems

How to Select All Cells Including Blanks with VBA in Excel

Khadija Khan Sep 30, 2026 870 views

Question details

The user needs a VBA macro to select a dynamic range starting from A2 to the last used row and column, ensuring empty cells within the boundaries are included, and the selection extends automatically as new rows are appended.

How to Select All Cells Including Blanks with VBA in Excel

Product: Excel
Device & OS: not provided
Scenario: Writing a VBA macro to dynamically highlight or manipulate a block of data that expands over time, without ignoring blank cells within the data grid.
Observed behavior: The goal is to automatically capture the entire required range (such as A2:Z1792) and have the script adaptively select A2:Z1793 or beyond when new data rows are added to the worksheet.

Before you start

Ensure you have the Developer tab enabled in your spreadsheet program and remember to save your workbook as a Macro-Enabled Workbook to preserve your VBA code.

Solution 1Recommended

Define Last Row Based on a Specific Column

Use this method if a specific column (e.g., Column Z) is a reliable indicator of the last row of your dataset, meaning it will always contain data in the final row.

This approach uses the End(xlUp) method to simulate pressing Ctrl + Up Arrow from the very bottom of the worksheet in a designated column. It determines the absolute last row of data and allows you to build a continuous range including all preceding empty cells.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

Click on 'Insert' in the top menu, then select 'Module' to create a blank script window.

Declare the Worksheet and Variables

Write your sub-routine and declare your variables. Set your target worksheet using 'Dim ws As Worksheet' and 'Set ws = ActiveSheet'.

Find the Last Row

Add the code to locate the last populated row in your target column (e.g., Column Z): 'lastRow = ws.Cells(ws.Rows.Count, "Z").End(xlUp).Row'.

Set and Select the Dynamic Range

Define the range starting from A2 down to your dynamic last row: 'Set rng = ws.Range("A2:Z" & lastRow)'. Finally, apply 'rng.Select' to highlight the entire block, including any blank cells in between.

Define Last Row Based on a Specific Column

Dynamic Expansion: Because the code calculates the last row every time the macro runs, the selected range will automatically expand when you add new rows to column Z.

Solution 2

Determine Last Row and Column Using the Find Method

Use this approach when the last populated cell might vary across different columns or rows, ensuring no data is missed even if some columns end with blank cells.

Advanced VBA Automation

Automate Dynamic Range Selection Using WPS Office VBA

WPS Spreadsheet fully supports Visual Basic for Applications (VBA), allowing you to seamlessly run dynamic range selection macros just like you would in Microsoft Excel.

1. Open WPS Spreadsheet: Launch WPS Office and open your .xlsm or .xlsx file.
2. Navigate to the Developer Tab: Click on the Developer tab in the top ribbon and select 'VBA Editor'.
3. Paste Your VBA Code: Insert a new module and paste your macro code using the End(xlUp) or Find methods to determine your dynamic range.
4. Run the Macro: Press F5 or click Run to automatically select your expanding data range, complete with blank cells included.

Fully compatible with Microsoft Excel VBA syntax and objectsEasily handles dynamic range scaling and complex automationSupports 100% compatibility with .xls, .xlsx, and .xlsm formatsLightweight and optimized for fast macro execution

Frequently Asked Questions

Why does End(xlUp) sometimes skip columns that are completely blank?

The End(xlUp) method simulates pressing Ctrl+Up Arrow in a specific column. If that entire column is completely blank, the cursor jumps all the way to row 1. To avoid this, always apply the End(xlUp) function to a column that acts as the primary key or reliably contains data in the last row of your dataset.

How can I automatically expand a selection without using VBA code?

If you prefer not to write macros, you can format your data as an official Excel Table by pressing Ctrl + T. Tables automatically expand to include new rows and columns. Alternatively, you can create a dynamic Named Range using the OFFSET and COUNTA functions in the Name Manager.

Will my dynamic range VBA code work if the data has hidden rows?

Yes, using the End(xlUp) or Find methods in VBA will generally capture the last row regardless of whether some rows in between are hidden, ensuring your defined range correctly encompasses the bounds of your data.

Excel & Data Problems