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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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

W
  • Question Question
Creating a 'Reset' button for a text form, plus additional buttons
Replies
2
Views
309
WEB78
W
imback2nite
  • Solved
VBA help.
2
Replies
10
Views
803
Peter_SSs
Peter_SSs
R
  • Solved
Hide rows according to checkboxes on seperate sheet
Replies
8
Views
632
John_w
J
N
  • Question Question
Hiding/showing rows between two values
Replies
1
Views
480
Joe4
Joe4
O
  • Question Question
Somewhat basic UDFs work except that they return #VALUE! error after hiding rows with VBA.
Replies
2
Views
129
OaklandJim
O

Forum statistics

Threads
1,224,823
Messages
6,181,175
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