Hiding a Huge Number of Rows

Julia would like to hide all the rows in a worksheet except rows 1-30. She is looking for the fastest, easiest way to do this.

If you don't have to do the hiding too often, the easiest method is the following, provided there is something in every cell of column A:

1. Select cell A31.
2. Press Shift+Ctrl+Down Arrow. All the cells from A31 through the last used cell in column A are selected.
3. Press Shift+Ctrl+Down Arrow again. The selection is extended through the very last cell in column A.
4. Hide your rows as you normally would. (Either right-click and hide that way or use the menus)

If you don't have data in all the cells of column A, then the following variation is probably the fastest method:

1. Select cell A31.
2. Press Shift+Ctrl+End. All the cells from A31 through the last used cell in the data table selected.
3. Press Shift+Ctrl+Down Arrow. The selection is extended through the very last row in the worksheet.
4. Hide your rows as you normally would. (Either right-click and hide that way or use the menus. You can also just press Ctrl+9.)

Here's another quick method that can be used:

1. In the Name Box (top-left corner of the worksheet, above column A), enter A31:A65536. Excel selects the range you entered.
2. Hide your rows as you normally would. (Either right-click and hide that way or use the menus/ribbon. You can also just press Ctrl+9.)

If you need to hide rows like this quite often, you could use the macro recorder to record any of the above techniques, or you could use a more flexible macro, like the following:

Sub HideRows()
    Dim r As Variant
    On Error GoTo Canceled
    r = InputBox("Rows to Hide:")
    Rows(r).EntireRow.Hidden = True
Canceled:
End Sub

The only caveat is that you need to remember to include a colon in the rows you specify for the macro. Thus, if you wanted to hide rows 31 through 543, you would enter 31:543.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5731) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Hiding a Huge Number of Rows.