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

L

liabilityquek

New Member
Joined
Oct 17, 2021
Messages
25
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
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



VBA Code: 
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
 
Upvote 0
Solution
SQUIDD said:
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



VBA Code: 
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
Click to expand...
Thanks SQUIDD! The code works like a charm!
 
Upvote 0
SQUIDD said:
Your welcome, thanks
Click to expand...
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?

VBA Code: 
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
 
Upvote 0
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


VBA Code: 
       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
 
Upvote 0
SQUIDD said:
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


VBA Code: 
       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
Click to expand...
Alright noted. Thanks for the explanation
 
Upvote 0
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
 
Upvote 0
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.

VBA Code: 
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
 
Upvote 0
SQUIDD said:
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.

VBA Code: 
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
Click to expand...
I didn`t know that. The code runs excelent. Thanks for your help
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
VBA Assistance - Hide / Unhiding a sheet based on cell contents
Replies
5
Views
381
V L
V
M
  • Solved
VBA Hide/Unhide Columns, different/multiple sheets
2 3
Replies
20
Views
556
Minx_Minxy
M
Y
  • Question Question
How to automatically hide/unhide columns based on related month with VBA
Replies
1
Views
367
ysfuyar
Y
N
  • Question Question
Hiding/showing rows between two values
Replies
1
Views
480
Joe4
Joe4
J
  • Question Question
Hide Row Based on cell value which more than 45 days
2
Replies
12
Views
735
JayHo
J

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top