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 total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
171
WEB78
W
imback2nite
  • Solved
VBA help.
2
Replies
10
Views
752
Peter_SSs
Peter_SSs
R
  • Solved
Hide rows according to checkboxes on seperate sheet
Replies
8
Views
520
John_w
J
N
  • Question Question
Hiding/showing rows between two values
Replies
1
Views
405
Joe4
Joe4
O
  • Question Question
Somewhat basic UDFs work except that they return #VALUE! error after hiding rows with VBA.
Replies
2
Views
81
OaklandJim
O

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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