Hide All Columns Expect
Highlighted
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.
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).
Sub Auto_Open()
Application.OnKey "{F9}", "Hide_all"
End Sub
Result is wonderful :
