VBA code for hiding rows does not work

L

liabilityquek

New Member
Joined
Oct 17, 2021
Messages
25
Office Version
  1. 2019
Platform
  1. Windows
Hi,

I have entered the VBA code to hide the respective rows if certain criteria is being met. However, the rows doesn't seem to hide or unhide. Please assist on which area did it gone wrong.

Thanks in advance!

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
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") = "FOREIGN COMPANY REGISTERED IN SINGAPORE" Then
                If Range("C7") = "No" Then
                
                Rows("38:41").EntireRow.Hidden = True
                Rows("34:35").EntireRow.Hidden = True
                Rows("30:31").EntireRow.Hidden = True
                Rows("28").EntireRow.Hidden = True
                
                Else
                Rows("32:33").EntireRow.Hidden = False
                Rows("36:37").EntireRow.Hidden = False
                Rows("29").EntireRow.Hidden = False



    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") = "FOREIGN COMPANY REGISTERED IN SINGAPORE" Then
                If Range("C7") = "Yes" Then
                
                Rows("34:37").EntireRow.Hidden = True
                Rows("40:41").EntireRow.Hidden = True
                Rows("30:31").EntireRow.Hidden = True
                Rows("28").EntireRow.Hidden = True
                
                Else
                Rows("32:33").EntireRow.Hidden = False
                Rows("38:39").EntireRow.Hidden = False
                Rows("29").EntireRow.Hidden = False
                
                
    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") = "FOREIGN COMPANY REGISTERED IN SINGAPORE" Then
                If Range("C7") = "Credit Re-assessment" Then
                
                Rows("34:39").EntireRow.Hidden = True
                Rows("30:31").EntireRow.Hidden = True
                Rows("28").EntireRow.Hidden = True
                                
                Else
                Rows("32:33").EntireRow.Hidden = False
                Rows("40:41").EntireRow.Hidden = False
                Rows("29").EntireRow.Hidden = False
                Rows("35").EntireRow.Hidden = False

End If
End If
End If
End If
End If
End If

End Sub
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Your If's are not being ended in the correct order, the way you have done it looks like it will only work when C7="No"
See if this works, remember that If in vba is case sensitive so "No" does not equal "no" or "NO"
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
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") = "FOREIGN COMPANY REGISTERED IN SINGAPORE" Then
        If Range("C7") = "No" Then
                
                Rows("38:41").EntireRow.Hidden = True
                Rows("34:35").EntireRow.Hidden = True
                Rows("30:31").EntireRow.Hidden = True
                Rows("28").EntireRow.Hidden = True
                
                Else
                Rows("32:33").EntireRow.Hidden = False
                Rows("36:37").EntireRow.Hidden = False
                Rows("29").EntireRow.Hidden = False

        ElseIf Range("C7") = "Yes" Then
                
                Rows("34:37").EntireRow.Hidden = True
                Rows("40:41").EntireRow.Hidden = True
                Rows("30:31").EntireRow.Hidden = True
                Rows("28").EntireRow.Hidden = True
                
                Else
                Rows("32:33").EntireRow.Hidden = False
                Rows("38:39").EntireRow.Hidden = False
                Rows("29").EntireRow.Hidden = False
                
        ElseIf Range("C7") = "Credit Re-assessment" Then
                
                Rows("34:39").EntireRow.Hidden = True
                Rows("30:31").EntireRow.Hidden = True
                Rows("28").EntireRow.Hidden = True
                                
                Else
                Rows("32:33").EntireRow.Hidden = False
                Rows("40:41").EntireRow.Hidden = False
                Rows("29").EntireRow.Hidden = False
                Rows("35").EntireRow.Hidden = False

        End If
End If


End Sub
 
Upvote 0
jasonb75 said:
Your If's are not being ended in the correct order, the way you have done it looks like it will only work when C7="No"
See if this works, remember that If in vba is case sensitive so "No" does not equal "no" or "NO"
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
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") = "FOREIGN COMPANY REGISTERED IN SINGAPORE" Then
        If Range("C7") = "No" Then
               
                Rows("38:41").EntireRow.Hidden = True
                Rows("34:35").EntireRow.Hidden = True
                Rows("30:31").EntireRow.Hidden = True
                Rows("28").EntireRow.Hidden = True
               
                Else
                Rows("32:33").EntireRow.Hidden = False
                Rows("36:37").EntireRow.Hidden = False
                Rows("29").EntireRow.Hidden = False

        ElseIf Range("C7") = "Yes" Then
               
                Rows("34:37").EntireRow.Hidden = True
                Rows("40:41").EntireRow.Hidden = True
                Rows("30:31").EntireRow.Hidden = True
                Rows("28").EntireRow.Hidden = True
               
                Else
                Rows("32:33").EntireRow.Hidden = False
                Rows("38:39").EntireRow.Hidden = False
                Rows("29").EntireRow.Hidden = False
               
        ElseIf Range("C7") = "Credit Re-assessment" Then
               
                Rows("34:39").EntireRow.Hidden = True
                Rows("30:31").EntireRow.Hidden = True
                Rows("28").EntireRow.Hidden = True
                               
                Else
                Rows("32:33").EntireRow.Hidden = False
                Rows("40:41").EntireRow.Hidden = False
                Rows("29").EntireRow.Hidden = False
                Rows("35").EntireRow.Hidden = False

        End If
End If


End Sub
Click to expand...
thank you for reverting Jason. I tried your code above and an error pops out showing compile error else without if
 
Upvote 0
I missed the unnecessary 'Else' lines in the code due to the way it was formatted. I think that this should be ok now but haven't had time to clean it up properly or test it.

I'll have another look at it later and clean it up properly for you, i'll wait until you've tested this first in case it needs any changes then I can do both at the same time.
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
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") = "FOREIGN COMPANY REGISTERED IN SINGAPORE" Then
        If Range("C7") = "No" Then
                
                Rows("38:41").EntireRow.Hidden = True
                Rows("34:35").EntireRow.Hidden = True
                Rows("30:31").EntireRow.Hidden = True
                Rows("28").EntireRow.Hidden = True
                
            
                Rows("32:33").EntireRow.Hidden = False
                Rows("36:37").EntireRow.Hidden = False
                Rows("29").EntireRow.Hidden = False

        ElseIf Range("C7") = "Yes" Then
                
                Rows("34:37").EntireRow.Hidden = True
                Rows("40:41").EntireRow.Hidden = True
                Rows("30:31").EntireRow.Hidden = True
                Rows("28").EntireRow.Hidden = True
                
        
                Rows("32:33").EntireRow.Hidden = False
                Rows("38:39").EntireRow.Hidden = False
                Rows("29").EntireRow.Hidden = False
                
        ElseIf Range("C7") = "Credit Re-assessment" Then
                
                Rows("34:39").EntireRow.Hidden = True
                Rows("30:31").EntireRow.Hidden = True
                Rows("28").EntireRow.Hidden = True
                                
        
                Rows("32:33").EntireRow.Hidden = False
                Rows("40:41").EntireRow.Hidden = False
                Rows("29").EntireRow.Hidden = False
                Rows("35").EntireRow.Hidden = False

        End If
End If


End Sub
 
Upvote 0
jasonb75 said:
I missed the unnecessary 'Else' lines in the code due to the way it was formatted. I think that this should be ok now but haven't had time to clean it up properly or test it.

I'll have another look at it later and clean it up properly for you, i'll wait until you've tested this first in case it needs any changes then I can do both at the same time.
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
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") = "FOREIGN COMPANY REGISTERED IN SINGAPORE" Then
        If Range("C7") = "No" Then
               
                Rows("38:41").EntireRow.Hidden = True
                Rows("34:35").EntireRow.Hidden = True
                Rows("30:31").EntireRow.Hidden = True
                Rows("28").EntireRow.Hidden = True
               
           
                Rows("32:33").EntireRow.Hidden = False
                Rows("36:37").EntireRow.Hidden = False
                Rows("29").EntireRow.Hidden = False

        ElseIf Range("C7") = "Yes" Then
               
                Rows("34:37").EntireRow.Hidden = True
                Rows("40:41").EntireRow.Hidden = True
                Rows("30:31").EntireRow.Hidden = True
                Rows("28").EntireRow.Hidden = True
               
       
                Rows("32:33").EntireRow.Hidden = False
                Rows("38:39").EntireRow.Hidden = False
                Rows("29").EntireRow.Hidden = False
               
        ElseIf Range("C7") = "Credit Re-assessment" Then
               
                Rows("34:39").EntireRow.Hidden = True
                Rows("30:31").EntireRow.Hidden = True
                Rows("28").EntireRow.Hidden = True
                               
       
                Rows("32:33").EntireRow.Hidden = False
                Rows("40:41").EntireRow.Hidden = False
                Rows("29").EntireRow.Hidden = False
                Rows("35").EntireRow.Hidden = False

        End If
End If


End Sub
Click to expand...
the code works! Appreciate the assistance Jason!
 
Upvote 0
You must log in or register to reply here.

Similar threads

imback2nite
  • Solved
VBA help.
2
Replies
10
Views
388
Peter_SSs
Peter_SSs
R
  • Solved
Hide rows according to checkboxes on seperate sheet
Replies
8
Views
171
John_w
J
M
  • Question
Hide Rows based on cell with drop down values
Replies
1
Views
478
kevin9999
K
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
502
kevin9999
K
J
  • Question
VBA Macro for hiding and unhiding rows is rearranging cells
2
Replies
14
Views
976
dmt32
D

Forum statistics

Threads
1,215,465
Messages
6,124,978
Members
449,200
Latest member
Jamil ahmed

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