Select Multiple Entire Rows with an Excel VBA Loop
Question details
Select a continuous span of entire rows in an Excel VBA macro instead of replacing the selection on each loop iteration.

- 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.
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.
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.
In Excel, open Developer > Visual Basic and locate the macro that selects the rows.
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
Run the macro from the VBA editor or Developer > Macros. Check that the entire row span is selected on the target worksheet.

Work directly with the entire row range
Use this approach when the macro needs to modify the rows rather than display a selection.
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.

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.




