VBA & Macro Problems

Select Multiple Entire Rows with an Excel VBA Loop

Natalie TaylorNatalie Taylor Nov 18, 2026 869 views

Question details

Select a continuous span of entire rows in an Excel VBA macro instead of replacing the selection on each loop iteration.

Select Multiple Entire Rows with an Excel VBA Loop
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A macro identifies a starting and ending row and needs to select all entire rows between them.
Observed behavior
Selecting an entire row during each iteration replaces the previous selection, leaving only the last row selected.
Before you start

Identify the target worksheet and the first and last row numbers. Make sure the row numbers form a valid continuous range and that macros are enabled for the workbook.

Solution 1Recommended

Select the entire row span with one qualified range

Use this approach when you need all rows between two row numbers selected at once.

A single range covers the full span, so there is no need to select each row in a loop. Qualifying the range with ws ensures it refers to the intended worksheet.

1
Open the VBA editor

In Excel, open Developer > Visual Basic and locate the macro that selects the rows.

2
Replace the repeated selections

Ensure ws refers to the target worksheet and find_no and find_period contain the first and last row numbers. Replace the loop's selection code with: With ws .Activate .Range("A" & find_no & ":A" & find_period).EntireRow.Select End With

3
Run the macro

Run the macro from the VBA editor or Developer > Macros. Check that the entire row span is selected on the target worksheet.

Select the entire row span with one qualified range
Why activate the worksheet?: Excel requires the worksheet containing a range to be active when you select that range. Activation is not necessary when you work directly with the range instead.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative for everyday spreadsheet work. Its familiar interface and Excel-format compatibility make it easy to move existing files into WPS Spreadsheets. It does not fix Excel's VBA selection behavior, and macro support may depend on your WPS version.

Select and edit spreadsheet rows through a familiar interfaceOpen and save Microsoft Excel formats such as XLSXUse a free, lightweight office suite for everyday tasksMove existing spreadsheet files into WPS with minimal disruption
microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA loop select only the last row?

Each EntireRow.Select call replaces the previous selection. Build one range from the first row to the last row and call EntireRow.Select once.

Why must I activate the worksheet before selecting its rows?

Excel can select a range only on the active worksheet. Use ws.Activate before selecting a range on ws, or avoid selection and operate on the qualified range directly.

How can I change the rows without selecting them?

Apply the operation directly to ws.Range("A" & find_no & ":A" & find_period).EntireRow. For example, set its Interior.Color property to change the rows' fill color.

Does this range select nonadjacent rows?

No. The range from find_no to find_period includes every row between those endpoints. For nonadjacent rows, build a combined range with VBA's Union function instead.