Hide and Unhide cell with multiple criteria

[Sophanith]

Dear All,

We want to create a VBA that can hide and unhide cell base on multiple criteria but the code below just hide only one criteria and it also cannot unhide the cell back. Please help me.

Set myrange = Range("B:B")
   For Each cell In myrange
   If cell <> "" Then
   If cell.Value = "AAAAA" Then
    cell.EntireRow.Hidden = True
    End If
    End If
    Next cell

 

[Hui]

Set myrange = Range("B:B")
   For Each cell In myrange
   If cell <> "" Then
   If cell.Value = "AAAAA" Then
    cell.EntireRow.Hidden = True
    else
    cell.EntireRow.Hidden = False
End If

    End If
    Next cell

 

[Hui]

Also I wouldn't use a range like: Range("B:B")
This will force VBA to check each 1,048,576 cells in column B

Better to use something like:

Set myRange=Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))

which will only process the cells in Column B with data

 

[Sophanith]

Dear Hui,

The script above i use with the check box and when i click on it it hide data but when i unclick it, it does not unhide it back. And I would like to add 3 more criteria in column B but I don't know how to add it. Could you please help me?

Thanks

 

[Debraj]

Hi sophanith..
a sample file is appreciabe..to know the structure.. and multiple criteria type..

 

[Hui]

 If cell.Value = "AAAAA" or cell.Value = "BBBBB" or cell.Value = "ccccc" Then

 

[Sophanith]

Dear Hui,

I have applied your code and it work perfect for hide row but when I unclick the check box it is not unhide it back. Please see attached file.

PS: Debraj, I sorry that I haven't upload attached file but now you can see the attached file that what i am working on.

Thanks,

Sophanith

 

[Debraj]

Hi Sophanith!

try this..

Private Sub CheckBox1_Click()
Dim myrange As Range
  Set myrange = Range("B1:B300")
  For Each cell In myrange
  If cell <> "" Then
  If cell.Value = "KHR (Riel)" Or cell.Value = "USD ($US)" Or cell.Value = "THB (Baht)" Then
  cell.EntireRow.Hidden = CheckBox1
  Else
  cell.EntireRow.Hidden = False
End If
  End If
  Next cell
End Sub

 

[Smallman]

Sophanith

I notice you are not using col A. You could use this formula there.

=IF(OR(B9="KHR (Riel)",B9="USD ($US)",B9="THB (Baht)"),1,0)

This VBA checkbox code.

Private Sub CheckBox1_Click()
If CheckBox1 = True Then [a8:a300].AutoFilter 1, 0, , , 0 Else: [a8].AutoFilter
End Sub

The code is simply filtering on the results of Col A. File Attached.

Take care

Smallman

 

[Sophanith]

Dear Hui, Debraj and Smallman

All your code are worked perfectly. Thanks a lot for your help. I really really appreciate you help.

Thanks

Sophanith