Range.EntireRow Property (Excel)
Returns a Range object that represents the entire row (or rows) that contains the specified range. Read-only.
This example sets the value of the first cell in the row that contains the active cell. The example must be run from a worksheet.
This 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 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