VBA code to hide row if two or more conditions are met

[liabilityquek]

Hi All

I am trying to work on a vba code to hide rows if 2 or more criteria have been met.

If Cell1 contains "Govt Agencies & Stat Board" and Cell2 contains "Yes", I would need to hide rows 5 rows
If Cell1 contains "Govt Agencies & Stat Board" and Cell2 contains "No", I would need to hide rows 5 rows
If Cell1 contains "Govt Agencies & Stat Board" and Cell2 contains "Credit Re-assessment", I would need to hide rows 5 rows

else
rows will not be hide

Thanks in advance!

 

[SQUIDD]

Hello

Please see below.

You say Cell1 contains and cell2 contains. I have assumed at this point you meant equals. But if you really did mean contain, then we can change it up.
So if cell2 value was "he said yes"
In my below example you need to add this into the sheet code, i have used CELL A1 and CELL B1

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("a1") = "Govt Agencies & Stat Board" Then
    If Range("b1") = "Yes" Or Range("b1") = "No" Or Range("b1") = "Credit Re-assessment" Then
        Rows("5:5").EntireRow.Hidden = True
    Else
        Rows("5:5").EntireRow.Hidden = False
    End If
End If
End Sub

 

[liabilityquek]

Hello

Please see below.

You say Cell1 contains and cell2 contains. I have assumed at this point you meant equals. But if you really did mean contain, then we can change it up.
So if cell2 value was "he said yes"
In my below example you need to add this into the sheet code, i have used CELL A1 and CELL B1

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("a1") = "Govt Agencies & Stat Board" Then
    If Range("b1") = "Yes" Or Range("b1") = "No" Or Range("b1") = "Credit Re-assessment" Then
        Rows("5:5").EntireRow.Hidden = True
    Else
        Rows("5:5").EntireRow.Hidden = False
    End If
End If
End Sub

Thanks SQUIDD! The code works like a charm!

 

[SQUIDD]

Your welcome, thanks

 

[liabilityquek]

Your welcome, thanks

Hi SQUIDD

The vba code works but then i would always need to click and run code every time in order to have the rows hidden. Is it to code in a such a way where the rows are automatically hidden?

Sub Twodiffcondition()

If Range("C16") = "Govt Agencies & Stat Board" Then
    If Range("C7") = "Yes" Or Range("C7") = "No" Or Range("C7") = "Credit Re-assessment" Then
        Rows("17:25").EntireRow.Hidden = True
        
    Else
        Rows("17:25").EntireRow.Hidden = False
        
    End If

End If

If Range("C16") = "Private Company Ltd by Shares" Or Range("C16") = "Exempt Company Ltd by Shares" Or Range("C16") = "Public Company Ltd by Shares" Or Range("C16") = "Sole Proprietorship" Or Range("C16") = "FOREIGN COMPANY REGISTERED IN SINGAPORE" Or Range("C16") = "Others" Then
    If Range("C7") = "Yes" Or Range("C7") = "No" Or Range("C7") = "Credit Re-assessment" Then
        Rows("17:25").EntireRow.Hidden = False

Else
        Rows("17:25").EntireRow.Hidden = False

End If

End If


End Sub

 

[SQUIDD]

Hi

So I guessed you missed it in my original code.

My code was related to the worksheet change event. Which would have done what you are now asking.

You have added my code into a standard module by the looks of it.

What you should ahve done is right click on your sheets tab at the bottom, click view code, and paste my code in there.

The code will then run everytime that worksheet changes.

untested below

but this HAS to go into a sheet module as suggested above

       Private Sub Worksheet_Change(ByVal Target As Range)
If Range("C16") = "Govt Agencies & Stat Board" Then
    If Range("C7") = "Yes" Or Range("C7") = "No" Or Range("C7") = "Credit Re-assessment" Then
        Rows("17:25").EntireRow.Hidden = True
        
    Else
        Rows("17:25").EntireRow.Hidden = False
        
    End If

End If

If Range("C16") = "Private Company Ltd by Shares" Or Range("C16") = "Exempt Company Ltd by Shares" Or Range("C16") = "Public Company Ltd by Shares" Or Range("C16") = "Sole Proprietorship" Or Range("C16") = "FOREIGN COMPANY REGISTERED IN SINGAPORE" Or Range("C16") = "Others" Then
    If Range("C7") = "Yes" Or Range("C7") = "No" Or Range("C7") = "Credit Re-assessment" Then
        Rows("17:25").EntireRow.Hidden = False

Else
        Rows("17:25").EntireRow.Hidden = False

End If

End If

End Sub

 

[liabilityquek]

Hi

So I guessed you missed it in my original code.

My code was related to the worksheet change event. Which would have done what you are now asking.

You have added my code into a standard module by the looks of it.

What you should ahve done is right click on your sheets tab at the bottom, click view code, and paste my code in there.

The code will then run everytime that worksheet changes.

untested below

but this HAS to go into a sheet module as suggested above

       Private Sub Worksheet_Change(ByVal Target As Range)
If Range("C16") = "Govt Agencies & Stat Board" Then
    If Range("C7") = "Yes" Or Range("C7") = "No" Or Range("C7") = "Credit Re-assessment" Then
        Rows("17:25").EntireRow.Hidden = True
       
    Else
        Rows("17:25").EntireRow.Hidden = False
       
    End If

End If

If Range("C16") = "Private Company Ltd by Shares" Or Range("C16") = "Exempt Company Ltd by Shares" Or Range("C16") = "Public Company Ltd by Shares" Or Range("C16") = "Sole Proprietorship" Or Range("C16") = "FOREIGN COMPANY REGISTERED IN SINGAPORE" Or Range("C16") = "Others" Then
    If Range("C7") = "Yes" Or Range("C7") = "No" Or Range("C7") = "Credit Re-assessment" Then
        Rows("17:25").EntireRow.Hidden = False

Else
        Rows("17:25").EntireRow.Hidden = False

End If

End If

End Sub

Alright noted. Thanks for the explanation

 

[lazarandreiflorin]

Hello everybody, I need something similar except I only need some Columns to be hidden if a cell contains a certain word.
When D5 = "Februarie" then Columns "AG:AI" need to be hidden, when D5 = "Aprilie" only Column "AI" needs to be hidden, when D5 = "Iunie" only Column "AI"needs to be hidden, when D5 = "Septembrie" only Column "AI" needs to be hidden, when D5 = "Noiembrie" only "AI"needs to be hidden and when D5 = nothing or another text none of the columns need to be hidden. And this has to work whenever changes are made to the D5 cell. I tried to do it myself but cannot do it. Thank you

 

[SQUIDD]

Hi

I know this is a similar question, but i would avoid asking different questions on an existing thread marked with a solution.
In the future i would suggest starting a new thread otherwise since this marked as solved, it wont get allot of interest.

But in any case i believe the below may work for you.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$D$5" Then
    Columns("AG:AI").EntireColumn.Hidden = False
    If Range("D5") = "Februarie" Then Columns("AG:AI").EntireColumn.Hidden = True
    If Range("D5") = "Aprilie" Then Columns("AI").EntireColumn.Hidden = True
    If Range("D5") = "Iunie" Then Columns("AI").EntireColumn.Hidden = True
    If Range("D5") = "Septembrie" Then Columns("AI").EntireColumn.Hidden = True
    If Range("D5") = "Noiembrie" Then Columns("AI").EntireColumn.Hidden = True
    If Range("D5") = "" Then Columns("AG:AI").EntireColumn.Hidden = False
    End If
End Sub

 

[lazarandreiflorin]

Hi

I know this is a similar question, but i would avoid asking different questions on an existing thread marked with a solution.
In the future i would suggest starting a new thread otherwise since this marked as solved, it wont get allot of interest.

But in any case i believe the below may work for you.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$D$5" Then
    Columns("AG:AI").EntireColumn.Hidden = False
    If Range("D5") = "Februarie" Then Columns("AG:AI").EntireColumn.Hidden = True
    If Range("D5") = "Aprilie" Then Columns("AI").EntireColumn.Hidden = True
    If Range("D5") = "Iunie" Then Columns("AI").EntireColumn.Hidden = True
    If Range("D5") = "Septembrie" Then Columns("AI").EntireColumn.Hidden = True
    If Range("D5") = "Noiembrie" Then Columns("AI").EntireColumn.Hidden = True
    If Range("D5") = "" Then Columns("AG:AI").EntireColumn.Hidden = False
    End If
End Sub

I didn`t know that. The code runs excelent. Thanks for your help