Hiding all but the selection

The example in this section hides all rows and columns except those in the current range selection. Figure 11-12 shows an example.

Figure 11-12: All rows and columns are hidden, except for a range (G8:K17).

Sub HideRowsAndColumns()

Dim row1 As Long, row2 As Long

Dim col1 As Long, col2 As Long

If TypeName(Selection) <> "Range" Then Exit Sub

' If last row or last column is hidden, unhide all and quit If Rows(Rows.Count).EntireRow.Hidden Or _

Columns(Columns.Count).EntireColumn.Hidden Then Cells.EntireColumn.Hidden = False Cells.EntireRow.Hidden = False Exit Sub End If row1 = Selection.Rows(1).Row row2 = row1 + Selection.Rows.Count - 1

col1 = Selection.Columns(1).Column col2 = col1 + Selection.Columns.Count - 1

Application.ScreenUpdating = False On Error Resume Next ' Hide rows

Range(Cells(1, 1), Cells(row1 - 1, 1)).EntireRow.Hidden = True Range(Cells(row2 + 1, 1), Cells(Rows.Count, 1)).EntireRow.Hidden = True ' Hide columns

Range(Cells(1, 1), Cells(1, col1 - 1)).EntireColumn.Hidden = True Range(Cells(1, col2 + 1), Cells(1, Columns.Count)).EntireColumn.Hidden = True End Sub

If the range selection consists of a noncontiguous range, the first Area is used as the basis for hiding rows and columns.

CD-ROM

A workbook with this example is available on the companion CD-ROM. The file is named % hide rows and columns.xlsm .

0 0

Post a comment