VBA - ToggleButton Hide/Unhide Rows

saechu · Aug 21st 2017

I'm new to VBA and am trying to figure out a way to do the following.
I have 3 spreadsheets (Live, Control, Compare). Live & Control have a list of names in D8:D99 and Compare has a list in A7:A98 with a number field in B7:B98.
I want to create a toggle button that will hide rows based on the criteria below (as well as unhide all rows if clicked again).
- If value in B is 0 AND
- Name in Compare is on both Live & Control lists

If the name in B7:B98 is missing from either Live or Control list or the value > 0, I do not want to hide the row.

Any help would be greatly appreciated. Thanks!

Max1616 · Aug 21st 2017

Re: VBA - ToggleButton Hide/Unhide Rows

Try this:

Code

Sub HideRows()


Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim lrow1 As Long
Dim CheckRow As Long
Dim i As Long
Dim mRow As Long




Set ws1 = Sheets("Compare")
Set ws2 = Sheets("Control")
Set ws3 = Sheets("Live")


lrow1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
CheckRow = 7 'Row which you want to start checking in ws1 (Compare)


For i = CheckRow To lrow1
    If ws1.Cells(i, 2) = 0 Then
        On Error Resume Next
        If WorksheetFunction.Match(ws1.Cells(i, 1), ws2.Columns(4), 0) > 0 And WorksheetFunction.Match(ws1.Cells(i, 1), ws3.Columns(4), 0) > 0 Then
            ws2.Rows(WorksheetFunction.Match(ws1.Cells(i, 1), ws2.Columns(4), 0)).Hidden = True
            ws3.Rows(WorksheetFunction.Match(ws1.Cells(i, 1), ws3.Columns(4), 0)).Hidden = True
        End If
    End If
Next i
      
End Sub


Sub UnhdieRows()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet


Set ws1 = ActiveSheet
Set ws2 = Sheets("Control")
Set ws3 = Sheets("Live")


ws2.Range(ws2.Rows(1), ws2.Rows(ws2.Cells(Rows.Count, 4).End(xlUp).Row)).Hidden = False
ws3.Range(ws3.Rows(1), ws3.Rows(ws3.Cells(Rows.Count, 4).End(xlUp).Row)).Hidden = False


End Sub

Display More

You can assign the code to a toggle button. (Let me know if you need help doing that). There are two macros here, one to hide all the rows that meet your criteria, and another to unhide all rows.

Let me know how it works!

VBA - ToggleButton Hide/Unhide Rows

Participate now!

Share