VideoPhoto

Showing posts with label unhide columns. Show all posts
Showing posts with label unhide columns. Show all posts

26 April, 2021

Unhide SOME of hidden Rows / Columns

It's quite easy to unhide a whole range of hidden columns or rows in a worksheet. But what if you want to unhide just one specific row or column, or a couple of them only.

Let's say, you've hidden e.g. columns from M to W, and rows from 30 to 40. Now you need to unhide columns S-T and rows 35-38 only. In such a situation probably the best way is a macro way. The following two macros can be very helpful:

Sub UnhideSomeRows()
'Unhide one or more of the hidden rows
Dim sRows As String
Repeat:
    sRows = Application.InputBox("Enter row number(s) to unhide", "Unhide row(s)", "e.g. 12 or 20:25")
    If sRows = "" Then Exit Sub
    On Error Resume Next
    If Err.Number <> 0 Then
        On Error GoTo 0
        Err.Clear
        MsgBox "Please input valid row(s)."
        GoTo Repeat
    End If
    Rows(sRows).EntireRow.Hidden = False
    MsgBox "Row(s) " & sRows & " is/are visible now", vbOKOnly, "Unhide specific Row(s)"
End Sub

Sub UnhideSomeColumns()
'Unhide one or more of the hidden columns
Dim sCols As String
Dim rRng As Range
Repeat:
    sCols = InputBox("Enter column(s) to unhide", "Unhide some of hidden Column(s)", "e.g. H or K:M")
    If sCols = "" Then Exit Sub
    On Error Resume Next
    Set rRng = ActiveSheet.Columns(sCols)
    If Err.Number <> 0 Then
        On Error GoTo 0
        Err.Clear
        MsgBox "Please input valid column(s)"
        GoTo Repeat
    End If
    rRng.EntireColumn.Hidden = False
    MsgBox "Column(s) " & UCase(sCols) & " is/are visible now", vbOKOnly, "Unhide specified Columns"
    Set rRng = Nothing
    On Error GoTo 0
End Sub

I've assigned them to separate Control buttons and gave them names "Unhide Rows" and "Unhide Columns". If you need help to do that, see this link: How to add a button and assign a macro .

I've then combined the two buttons into one, by moving and grouping them together as illustrated in this figure:

 

To unhide any columns or rows in my worksheet I just click on corresponding line of text on the button.