Hide All Except For The Selected Cells

Excel VBA Programming Technology

excel  vba sample Hide All Columns Expect

In some cases, we just want to see the cells that we’ve selected and focus our attention on those cells.
The easiest way to do this is to hide the cells except for the cells that we have selected.

With VBA codes, the cells we select easily can be hidden or displayed. We created a macro for to hide cells (rows and columns) and ensured this macro run by pressing F9 key on the keyboard. We added a Module on the Visual Basic Editor and created the following macro in this module to hide columns and rows external the selected cells. VBA Code to hide columns except highlighted

Sub Hide_all()
    Dim i, a As Long, s, t As Integer
    If TypeName(Selection) <> "Range" Then Exit Sub
    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
    i = Selection.Rows(1).Row
    a = i + Selection.Rows.Count - 1
    s = Selection.Columns(1).Column
    t = s + Selection.Columns.Count - 1
    Application.ScreenUpdating = False
    On Error Resume Next
    Range(Cells(1, 1), Cells(i - 1, 1)).EntireRow.Hidden = True
    Range(Cells(a + 1, 1), Cells(Rows.Count, 1)).EntireRow.Hidden = True

    Range(Cells(1, 1), Cells(1, s - 1)).EntireColumn.Hidden = True
    Range(Cells(1, t + 1), Cells(1, Columns.Count)).EntireColumn.Hidden = True
    Application.ScreenUpdating = True
End Sub

We added the following code snippet to Auto_Open() procedure in the Module to trigger the macro by pressing F9 key (with Auto_Open procedure, when the workbook is opened, we can automatically run the macro that we want).vba run macro on key press

Sub Auto_Open()
Application.OnKey "{F9}", "Hide_all"
End Sub

excel trigger macro with keyboard key In some laptop models may require pressing Fn key before F9 key.

Result is wonderful : excel hide columns but highlighted

­čôą Download sample workbook

Leave a Reply

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