Skip to main content

Range.EntireRow property

Table of contents
  1. Syntax
  2. Example

Returns a Range object that represents the entire row (or rows) that contains the specified range. Read-only.



expression A variable that represents a Range object.


The following example sets the value of the first cell in the row that contains the active cell. The example must be run from a worksheet.

ActiveCell.EntireRow.Cells(1, 1).Value = 5

The following example sorts all the rows on a worksheet, including hidden rows.

Sub SortAll()
    'Turn off screen updating, and define your variables.
    Application.ScreenUpdating = False
    Dim lngLastRow As Long, lngRow As Long
    Dim rngHidden As Range
    'Determine the number of rows in your sheet, and add the header row to the hidden range variable.
    lngLastRow = Cells(Rows.count, 1).End(xlUp).Row
    Set rngHidden = Rows(1)
    'For each row in the list, if the row is hidden add that row to the hidden range variable.
    For lngRow = 1 To lngLastRow
        If Rows(lngRow).hidden = True Then
            Set rngHidden = Union(rngHidden, Rows(lngRow))
        End If
    Next lngRow
    'Unhide everything in the hidden range variable.
    rngHidden.EntireRow.hidden = False
    'Perform the sort on all the data.
    Range("A1").CurrentRegion.Sort key1:=Range("A2"), order1:=xlAscending, Header:=xlYes
    'Re-hide the rows that were originally hidden, but unhide the header.
    rngHidden.EntireRow.hidden = True
    Rows(1).hidden = False
    'Turn screen updating back on.
    Set rngHidden = Nothing
    Application.ScreenUpdating = True
End Sub

The following example unhides all rows.

Rows.EntireRow.Hidden = False

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>