Unhide hidden rows with active button

Hi there,

Define Rows 13-17 of your worksheet as a Named Range called "ptrRowsToHide", and then see if the following code does what you need:

Option Explicit


Sub ShowRow()
    Call ShowOrHide(bShowRow:=True)
End Sub


Sub HideRows()
    Call ShowOrHide(bShowRow:=False)
End Sub


Sub ShowOrHide(bShowRow As Boolean)

    Const sROWS_TO_HIDE As String = "ptrRowsToHide"
    Const sSHEET_NAME   As String = "Sheet1"

    Dim rRowsToHide As Range
    Dim rRow        As Range
    Dim wks         As Worksheet

    Set wks = ThisWorkbook.Worksheets(sSHEET_NAME)

    With wks.Range(sROWS_TO_HIDE)

        If bShowRow = True Then

              For Each rRow In .Rows

                  If rRow.Hidden = True Then
                      rRow.Hidden = False
                      Exit For
                  End If

              Next rRow

        Else: .EntireRow.Hidden = True

        End If

    End With

End Sub

The highlighted values may be altered to suit your requirements.


Regarding:

If you know a way to make the code of the second macro react to rows being added above it . . .

the most straightforward way to approach this is to define the rows "further down" the worksheet as a Named Range, and to have your VBA code reference rows in the Named Range rather than specifically numbered rows on the worksheet.


Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi all,

Thank you for your answers. The command button works for un-hiding rows 13 to 17 by clucking it. I was trying to make it do the same for columns D to H, however, for some reason it does not work. Please find the code below. Would appreciate any help.

Private Sub CommandButton1_Click()

Dim thisRow As Long
For thisRow = 13 To 17
    If ActiveSheet.Rows(thisRow).EntireRow.Hidden Then
        ActiveSheet.Rows(thisRow).EntireRow.Hidden = False
        Exit For
    End If
Next thisRow

Dim thisColumn As Long
For thisColumn = ("D:D") To ("H:H")
    If ActiveSheet.Columns(thisColumn).EntireColumn.Hidden Then
        ActiveSheet.Columns(thisColumn).EntireColumn.Hidden = False
        Exit For
    End If
Next thisColumn
End Sub

Thank you!

Hi guys,

I managed to get it to work - the issue was that the columns had to be defined not as D to H, but rather as 4 to 8.

Now the last problem I have is that I have created another button that is supposed to hide rows 13 to 17 and columns D to H when clicked, however, I cannot seem to get it to work. Any suggestions? It should only hide the rows - not make them appear.

Thank you!

Best,
Alex

Hi,

Everything works now! Thanks all for the help!

Private Sub CommandButton3_Click()
    Rows("13:17").EntireRow.Hidden = True
    Columns("D:H").EntireColumn.Hidden = True
End Sub

Best,
Alex

Hi again,

Many thanks for your feedback,

You're welcome - glad I was able to help.

Regards,

Greg M