# VBA Hide rows based on button selection



## anand3dinesh (Dec 20, 2022)

Hi Please some one help me to find the solution for the below question?

I have 2 sheets called Buttons and Database as shown in the below image.
Upon button selection I would like to show only selected Name/Skills in Database sheet.

eg: If "Dinesh" button selected show only Dinesh rows in Database
      If "Excel" Button selected then i would like to show only Excel rows in Database
      IF "Dinesh" and "Excel" both button selected then I would like to show only Dinesh and Excel in Database.

I hope my question is clear.
Thanks in advance


----------



## tj4242 (Dec 20, 2022)

I am not sure this is what you are after but here is a shot.  Name is in column A on sheet Database.  Skills are in Column B.

Also, wouldn't this be easier without the buttons or VBA and instead just use a filter?


```
Sub Button1_Click()

    ' Dinesh Button
    
    Dim numRows As Long
    
    numRows = Sheets("Database").Range("A:A").End(xlDown).Row           'find the last row

    For i = 2 To numRows                                                'loop all rows
        If Sheets("Database").Cells(i, 1) = "Dinesh" Then               'if this is the person we are looking for
           Sheets("Database").Cells(i, 1).Font.Color = vbBlack          'show their name
           Sheets("Database").Cells(i, 2).Font.Color = vbBlack          'show their skill
        Else                                                            'not our person
            Sheets("Database").Cells(i, 1).Font.Color = vbWhite         'Hide their name
            Sheets("Database").Cells(i, 2).Font.Color = vbWhite         'hide their skill
        End If
    Next
End Sub
Sub Button2_Click()

    ' Harry Button
    
    Dim numRows As Long
    
    numRows = Sheets("Database").Range("A:A").End(xlDown).Row

    For i = 2 To numRows
        If Sheets("Database").Cells(i, 1) = "Harry" Then
           Sheets("Database").Cells(i, 1).Font.Color = vbBlack
           Sheets("Database").Cells(i, 2).Font.Color = vbBlack
        Else
            Sheets("Database").Cells(i, 1).Font.Color = vbWhite
            Sheets("Database").Cells(i, 2).Font.Color = vbWhite
        End If
    Next
End Sub
Sub Button3_Click()
    ' Excel Button
    
    Dim numRows As Long
    
    numRows = Sheets("Database").Range("A:A").End(xlDown).Row

    For i = 2 To numRows

        If Sheets("Database").Cells(i, 2) = "Excel" And Sheets("Database").Cells(i, 1).Font.Color = vbBlack Then
            'if this is the skill and the persons name is visible
           Sheets("Database").Cells(i, 2).Font.Color = vbBlack
        Else
            Sheets("Database").Cells(i, 2).Font.Color = vbWhite
            Sheets("Database").Cells(i, 1).Font.Color = vbWhite
        End If
        
    Next
End Sub
```


----------



## anand3dinesh (Dec 21, 2022)

tj4242 said:


> I am not sure this is what you are after but here is a shot.  Name is in column A on sheet Database.  Skills are in Column B.
> 
> Also, wouldn't this be easier without the buttons or VBA and instead just use a filter?
> 
> ...


Thanks for response but, It works only when one button is clicked. i don't think it works when both Name and Skill selected?


----------



## tj4242 (Dec 21, 2022)

It works for me.  It obviously needs more work because there are only 3 buttons implemented.



The Dinesh button call Button1_Click().  The Harry button calls Button2_Click() and the Excel button calls Button3_Click()

When I click the Excel button I get




When click the Dinesh button




When I click the Dinesh and then the Excel I get


----------



## anand3dinesh (Dec 21, 2022)

tj4242 said:


> It works for me.  It obviously needs more work because there are only 3 buttons implemented.
> View attachment 81327
> The Dinesh button call Button1_Click().  The Harry button calls Button2_Click() and the Excel button calls Button3_Click()
> 
> ...


I figured it out, it requires only 2 subs to do that one sub for Name col and one to Skills Col.
I want to hide the entire row not just match colour property as background.

i used autofilter method and it worked.


----------



## anand3dinesh (Dec 21, 2022)

tj4242 said:


> It works for me.  It obviously needs more work because there are only 3 buttons implemented.
> View attachment 81327
> The Dinesh button call Button1_Click().  The Harry button calls Button2_Click() and the Excel button calls Button3_Click()
> 
> ...


Thanks for your efforts


----------

