Excel VBA Rows: Select, Count, and Manage Worksheet Rows

VBA & macros · Updated November 18, 2026

In Excel VBA, Rows refers to worksheet rows or the rows within a specified range. For example, Worksheets("Data").Rows(5) addresses the entire fifth row on the Data worksheet; use a worksheet qualifier to make clear which sheet your code is working with.

What Rows Means in Excel VBA

Rows is a property that returns a collection of rows as a range. You can use it to inspect, format, copy, insert, hide, or delete rows through VBA. A worksheet has 1,048,576 rows in current desktop versions of Excel, numbered from 1 at the top to 1,048,576 at the bottom.

Each item returned by a worksheet’s Rows property represents a full worksheet row, not just the cells that contain data. This matters when you format or delete a row: the action applies across the row, including columns that appear empty. The row number passed to Rows is one-based, so Rows(1) means the first row.

For example, with a worksheet named Data, the following expressions refer to the first and fifth worksheet rows:

  • Worksheets("Data").Rows(1) refers to the first row.
  • Worksheets("Data").Rows(5) refers to the fifth row.
  • Worksheets("Data").Rows("5:10") refers to rows 5 through 10.

Basic Syntax and Worksheet References

You can write Rows(5) when you intend to use the active sheet, but that shorthand depends on which sheet happens to be active when the macro runs. A safer pattern is to set a worksheet variable and use its Rows property. The following example prints the worksheet’s row count and the address of its fifth row in the Immediate window in the VBA editor.

Save a copy of the workbook before running a macro. VBA changes generally cannot be reversed with Ctrl+Z.

Sub InspectRows()
    Dim ws As Worksheet

    ' Use a named worksheet rather than relying on the active sheet.
    Set ws = ThisWorkbook.Worksheets("Data")

    Debug.Print ws.Rows.Count
    Debug.Print ws.Rows(5).Address
End Sub

ThisWorkbook means the workbook containing the running code. If the code is stored in a different workbook from the data, you may need to refer to the data workbook explicitly instead. Also, a misspelled worksheet name causes a run-time error, so check the sheet tab’s name.

Rows on a Worksheet Versus Rows in a Range

The meaning of Rows depends on the object before the period. With a worksheet, ws.Rows(5) identifies worksheet row 5. With a range, Range("B2:D10").Rows(1) identifies the first row of that range, namely cells B2:D2. It does not mean worksheet row 1.

This distinction also affects Rows.Count. For ws.Rows.Count, the result is the number of rows on the whole worksheet: 1,048,576 in current Excel worksheets. For ws.Range("B2:D10").Rows.Count, the result is 9 because the range spans nine rows. The count tells you how many rows are in the object; it does not necessarily tell you the worksheet row number where the range ends.

If you need the entire worksheet row that intersects a cell or a range, use EntireRow. For example, ws.Range("C7").EntireRow represents all of worksheet row 7. This is useful when a search or calculation identifies a cell but the action needs to affect its full row.

Common Tasks: Select, Format, Copy, and Hide

Most row operations do not require selecting the row first. Directly referring to a row is usually clearer and avoids changing the user’s selection. These examples show common actions; use the named worksheet pattern so the code does not depend on the active sheet.

  • Format a row: ws.Rows(5).Font.Bold = True makes the fifth row’s text bold.
  • Set a row height: ws.Rows(5).RowHeight = 24 sets that row’s height to 24 points.
  • Hide a group: ws.Rows("3:5").Hidden = True hides rows 3 through 5. Set Hidden = False to show them again.
  • Copy a row: ws.Rows(5).Copy Destination:=ws.Rows(10) copies row 5 to row 10.
  • Insert a row: ws.Rows(5).Insert Shift:=xlDown inserts a row at row 5 and shifts existing cells down.

Copying or inserting entire rows can affect formulas, formatting, tables, and other content in those rows. If you only intend to work with a small block of cells, use a narrower range instead of the entire row. Similarly, hiding a row changes its visibility, not the underlying values; formulas can still refer to hidden cells.

Finding the Last Used Row

A common VBA task is finding where data ends. Avoid treating ws.Rows.Count as the last used row: it returns the worksheet’s maximum row count, even when the sheet contains only a few records. A familiar approach is to start at the bottom of a column and move upward to the last non-empty cell.

With records in column A and a header in row 1, the following example reports the last non-empty cell in column A. If A2:A20 contains data and A21:A1,048,576 is empty, the result is 20.

Sub FindLastRowInColumnA()
    Dim ws As Worksheet
    Dim lastRow As Long

    ' Change Data to the name of the worksheet you want to check.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Debug.Print lastRow
End Sub

This method answers a specific question: what is the last non-empty cell in column A? It can return row 1 if the column has only a header, and it will miss records that exist only in other columns. Choose a column that is reliably populated for every record, or use a different search strategy when your data is irregular. If formatting remains below the data, that alone does not make this particular column-based check treat those formatted cells as values.

Deleting Rows Safely in a Loop

When deleting multiple rows based on a condition, loop from the bottom upward. If you delete from the top downward, the remaining rows shift up, and a loop that increments its row number can skip the next record. The example below checks column A and deletes rows where that cell is empty, starting at row 2 so the header remains.

Save a copy of the workbook before running this destructive macro; deleted rows cannot be restored with Ctrl+Z after VBA runs.

Sub DeleteRowsWithBlankColumnA()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long

    ' Keep row 1 as a header and check records in column A.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For r = lastRow To 2 Step -1
        If Len(ws.Cells(r, "A").Value) = 0 Then
            ws.Rows(r).Delete
        End If
    Next r
End Sub

This code only scans as far as the last non-empty cell in column A. A blank in column A below that point will not be visited, even if another column has data on that row. For a real dataset, make sure the condition and the column used to determine lastRow match how the data is structured. Test deletion logic on a copy first.

Common Mistakes and How to Avoid Them

  • Using an unqualified Rows reference: Rows(5) uses the active sheet. Qualify it as ws.Rows(5) when the target sheet must be predictable.
  • Using Integer for a row number: VBA’s Integer type cannot hold row numbers as high as Excel’s worksheet limit. Use Long for row indexes and row counts.
  • Confusing a count with a row number: Range("B2:D10").Rows.Count is 9, not 10. It counts rows in the range rather than returning the final worksheet row.
  • Expecting hidden rows to be excluded: Rows.Count counts rows whether they are visible or hidden. Check visibility separately if a task should operate only on visible rows.
  • Deleting while looping downward: Deleting a row shifts later rows, which can cause records to be skipped. Count backward for deletion loops.
  • Assuming a blank cell means a blank record: A row can have an empty cell in one column and valid data in another. Base the test on the actual rule for identifying a record.

Performance and Choosing the Right Approach

For a small worksheet, a straightforward loop over rows is often easy to read and maintain. On a large dataset, repeatedly deleting rows can be slow because each deletion changes the worksheet. Consider whether you can filter the data, mark rows to keep, or build a result on another sheet instead. The best choice depends on whether you must preserve formatting, formulas, filters, and other worksheet features.

Use Rows when the operation genuinely applies to whole rows, such as hiding a record or deleting an entire record row. Use a specific range when only certain columns should change. For example, formatting ws.Range("A5:D5") affects four cells, whereas formatting ws.Rows(5) applies to the full row. Narrow references can make a macro’s purpose clearer and reduce unintended changes.

Excel Version and Platform Notes

VBA macros run in desktop Excel, including Microsoft 365 for Windows and Mac, though menus and shortcuts can differ. Excel for the web can open many macro-enabled workbooks, but it cannot create, edit, or run VBA macros; use the desktop application for those tasks. The row concepts and examples above apply to desktop Excel, although a workbook’s contents and security settings can affect whether a macro is available to run.

On Windows, open the VBA editor with Alt+F11; on Mac, the editor shortcut is commonly Option+F11, though some keyboards require the Fn key as well. You can also open the editor from the Developer tab by choosing Visual Basic. If the Developer tab is not shown on Windows, enable it through File > Options > Customize Ribbon. On Mac, use Excel > Settings > Ribbon & Toolbar to show Developer. Macro security controls whether code can run, so only enable macros from workbooks you trust.

Frequently Asked Questions

What does Rows(1) mean in Excel VBA?

On a worksheet object, Rows(1) refers to the entire first worksheet row. On a range object, Rows(1) refers to the first row within that range, which may be lower down the worksheet.

What is the difference between Rows and Range in VBA?

Rows addresses full rows or the row slices of a range. Range can address cells, cell blocks, columns, or rows by address. Use Rows for whole-row operations and a specific Range when only part of a row should be affected.

How do I count the rows in a worksheet?

Use ws.Rows.Count to get the total number of rows on a worksheet. In current Excel worksheets, that is 1,048,576. To count rows in a smaller range, use that range’s Rows.Count property instead.

How do I select a row in Excel VBA?

You can use ws.Rows(5).Select to select row 5 on a worksheet, but selection is usually unnecessary. Direct actions such as ws.Rows(5).Font.Bold = True work without changing the active selection.

Can I use VBA Rows in Excel for the web?

No. Excel for the web does not run or edit VBA macros. Open the workbook in desktop Excel for Windows or Mac to use VBA; the code examples use the desktop Excel object model.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.