VBA hide rows based on the value of 2 cells

[RuiFlora]

Hello. I am trying to build a code which hides entire rows based on the value of two cells. I put the code and an example to a better understanding of my issue. The code is not working, it is giving me an error.

CODE:
Private Sub Hide_Click()


RowNumber = 1


Do
DoEvents
RowNumber = RowNumber + 1


Rng = Sheets("test").Range("A" & RowNumber & ":" & "B" & RowNumber)
Search = Sheets("test").Range("C1")

If InStr(Rng, Search) >= 1 Then
Sheet2.Rows(RowNumber & ":" & row_number).EntireRow.Hidden = True
End If
Loop Until Rng = ""


MsgBox "Done"


End Sub

EXAMPLE:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Spain[/TD]
[/TR]
[TR]
[TD]Spain[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Spain[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Spain[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Spain[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In sum, I have a button which call the code. The code is supposed to hide all the rows based on the cell C1 (with "spain") and that does not contain the "x" in Column B. In that case hide the row 4 only. Eventually, if i put France in C2, hide the rows 7 and 8. The code i have is far from correct but I am really stuck right now.
Hope someone can help me !

Thanks in advance

 

[royUK]

Is the data in a table format. If so then AutoFilter would probably be the fastest way.

Can you share a file using OneDrive, DropBox, Cubby or similar

 

[RuiFlora]

Is the data in a table format. If so then AutoFilter would probably be the fastest way.

Can you share a file using OneDrive, DropBox, Cubby or similar

yes, the data is in table format, the normal format.
How can i share it via dropbox?

 

[royUK]

If you have a DropBox account which is free you can post a link to the file.

If not try recording code whilst you autofilter the data

 

[RuiFlora]

If you have a DropBox account which is free you can post a link to the file.

If not try recording code whilst you autofilter the data

The link is https://www.dropbox.com/s/c83xnat1wa78zc1/Test.xlsm

Thanks

 

[royUK]

Try this

Private Sub Hide_Click()


    Dim rng As Range


    With Sheets("test")
        Set rng = .Range("A2").CurrentRegion
        If Not ActiveSheet.AutoFilterMode Then rng.AutoFilter


        rng.AutoFilter Field:=1, Criteria1:=Sheets("test").Range("C1")
        rng.AutoFilter Field:=2, Criteria1:="<>"
    End With
    MsgBox "Done"


End Sub

 

[RuiFlora]

Try this

Private Sub Hide_Click()


    Dim rng As Range


    With Sheets("test")
        Set rng = .Range("A2").CurrentRegion
        If Not ActiveSheet.AutoFilterMode Then rng.AutoFilter


        rng.AutoFilter Field:=1, Criteria1:=Sheets("test").Range("C1")
        rng.AutoFilter Field:=2, Criteria1:="<>"
    End With
    MsgBox "Done"


End Sub

That worked. I just to make adjustments to undo the filters and so on.

Many thanks for your help!!