Trying to create a VBA macro in excel to hide/unhide specific rows

Question

I'm attempting to write a VBA macro that I can attach to a clickable button on an excel spreadsheet.

I need this button to hide and unhide specific rows in the spreadsheet if the word "Comments" is found in a specified column.

I have pieced together something that will do half the job, ie. hide the necessary columns. But I'm a bit stuck as to how to get it to reverse this action upon a second click.

This is what I have so far, but unsure how to proceed from here. Will hide the necessary rows effectively, but I need to be able to unhide them with the same button

Sub HideRows()
    Dim rCheck As Range
    Dim rHide As Range
    Dim rCheckCell As Range
    Set rCheck = Range("B18:B8000")
       
    rCheck.EntireRow.Hidden = False
   Set rHide = rCheck.Offset(rCheck.Rows.Count).Resize(1)
   
    For Each rCheckCell In rCheck.Cells
        If InStr(1, rCheckCell, "Comments", vbTextCompare) > 0 Then Set rHide = Union(rHide, rCheckCell)
    Next rCheckCell
    If Not rHide Is Nothing Then rHide.EntireRow.Hidden = True
   
End Sub

Try Application.ScreenUpdating = False before your code then set it to True afterwards. — MT1
– MT1, Commented Apr 5, 2024 at 4:18
If Not rHide Is Nothing Then rHide.EntireRow.Hidden = Not rHide.EntireRow.Hidden maybe? — MILO
– MILO, Commented Apr 5, 2024 at 4:42

taller · Accepted Answer · 2024-04-05 04:38:30Z

1

Set rHide = rCheck.Offset(rCheck.Rows.Count).Resize(1) - Don't add irrelated cell into rHide. Use If clause + Union to combine the desired range.

Pls try.

Sub ToggleHideRows()
    Dim rCheck As Range
    Dim rHide As Range
    Dim rCheckCell As Range
    Set rCheck = Range("B18:B8000")
    For Each rCheckCell In rCheck.Cells
        If InStr(1, rCheckCell, "Comments", vbTextCompare) > 0 Then
            If rHide Is Nothing Then
                Set rHide = rCheckCell
            Else
                Set rHide = Union(rHide, rCheckCell)
            End If
        End If
    Next rCheckCell
    If Not rHide Is Nothing Then
        rHide.EntireRow.Hidden = Not (rHide.Cells(1).EntireRow.Hidden)
    End If
End Sub

edited Apr 5, 2024 at 4:38

answered Apr 5, 2024 at 4:25

taller

19.7k2 gold badges9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jake M Over a year ago

I thought I left a comment on this. Worked perfectly. However, in a devastating turn of events, a few days after I got this working, along with several other macros that I had invested my time and effort into creating, the IT department at my organisation disabled macros on our group policy. So this was all for nothing. Thank you though

Black cat · Accepted Answer · 2024-04-05 04:45:18Z

This mod compare the range visible and total cells count and if not equal (there are hidden rows) unhide all rows in the range.

Sub HideRows()
    Dim rCheck As Range
    Dim rHide As Range
    Dim rCheckCell As Range
    Set rCheck = Range("B18:B8000")
    
    If rCheck.SpecialCells(xlCellTypeVisible).Count <> rCheck.Cells.Count Then  'added

        rCheck.EntireRow.Hidden = False    'added

    Else                          'added
   
        rCheck.EntireRow.Hidden = False
        Set rHide = rCheck.Offset(rCheck.Rows.Count).Resize(1)
   
        For Each rCheckCell In rCheck.Cells
            If InStr(1, rCheckCell, "Comments", vbTextCompare) > 0 Then Set rHide = Union(rHide, rCheckCell)
        Next rCheckCell
        If Not rHide Is Nothing Then rHide.EntireRow.Hidden = True
    End If        'added

End Sub

Collectives™ on Stack Overflow

Trying to create a VBA macro in excel to hide/unhide specific rows

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related