How to have multiple If target.Count large

J

JessicaKinnear

New Member
Joined
Jan 6, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Hey everyone, I am trying to set up the following code in my spreadsheet:

Code: 
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "C31" Then
      Sheets("LL Shortfall").Rows("20:59").Hidden = True
      Sheets("LL Shortfall").Rows(20).Resize(Target.Value).Hidden = False
   End If

There is already this code on the sheet which references various other sheets and it works fine. However if I try to add the code above it doesn't work, and it stops all the other codes from working. Here are the codes already in the sheet

VBA Code: 
Sub Jessica()
   Dim i As Long
   
   For i = 1 To 40
      Sheets("Tenant " & i).Visible = Sheets("INPUTS-BUILDING DETAILS").Range("E" & i + 38) <> ""
   Next i
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "C31" Then
      Rows("39:78").Hidden = True
      Rows(39).Resize(Target.Value).Hidden = False
   End If
    
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "C31" Then
      Sheets("INTER APPORTIONMENT").Rows("27:66").Hidden = True
      Sheets("INTER APPORTIONMENT").Rows(27).Resize(Target.Value).Hidden = False
   End If
   
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "C31" Then
      Sheets("INTER APPORTIONMENT").Rows("75:114").Hidden = True
      Sheets("INTER APPORTIONMENT").Rows(75).Resize(Target.Value).Hidden = False
   End If
    
   If Target.Address = ("$C$11") Then
        If Target.Text = "NET" Then
            Sheets("Budget DETAILED").Columns("D").EntireColumn.Hidden = True
            Sheets("Budget DETAILED").Columns("F").EntireColumn.Hidden = True
            Sheets("Tenant 1").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 2").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 3").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 4").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 5").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 6").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 7").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 8").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 9").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 10").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 11").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 12").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 13").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 14").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 15").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 16").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 17").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 18").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 19").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 20").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 21").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 22").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 23").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 24").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 25").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 26").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 27").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 28").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 29").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 30").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 31").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 32").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 33").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 34").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 35").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 36").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 37").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 38").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 39").Columns("H").EntireColumn.Hidden = True
            Sheets("Tenant 40").Columns("H").EntireColumn.Hidden = True
        ElseIf Target.Text = "GROSS" Then
            Sheets("Budget DETAILED").Columns("D").EntireColumn.Hidden = False
            Sheets("Budget DETAILED").Columns("F").EntireColumn.Hidden = False
            Sheets("Tenant 1").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 2").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 3").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 4").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 5").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 6").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 7").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 8").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 9").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 10").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 11").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 12").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 13").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 14").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 15").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 16").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 17").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 18").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 19").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 20").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 21").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 22").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 23").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 24").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 25").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 26").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 27").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 28").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 29").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 30").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 31").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 32").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 33").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 34").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 35").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 36").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 37").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 38").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 39").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 39").Columns("H").EntireColumn.Hidden = False
            Sheets("Tenant 40").Columns("H").EntireColumn.Hidden = False
        End If
   End If
End Sub

Does anyone have any idea why I can't add the other code to it, or how I can?

Thanks in advance!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
You're telling it to exit so that is probably what it is doing. It might be better to tell the code what to do when the result is =1 rather than >1
For example
VBA Code: 
If Target.CountLarge = 1 Then
    If Target.Address(0, 0) = "C31" Then
        Sheets("LL Shortfall").Rows("20:59").Hidden = True
        Sheets("LL Shortfall").Rows(20).Resize(Target.Value).Hidden = False
    End If
End If
This change needs to be applied to every section that uses countlarge to exit the procedure.
 
Upvote 0
As the 1st part of your code is just looking at C31 how about
VBA Code: 
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "C31" Then
      Rows("39:78").Hidden = True
      Rows(39).Resize(Target.Value).Hidden = False
      
      Sheets("LL Shortfall").Rows("20:59").Hidden = True
      Sheets("LL Shortfall").Rows(20).Resize(Target.Value).Hidden = False
      
      Sheets("INTER APPORTIONMENT").Rows("27:66").Hidden = True
      Sheets("INTER APPORTIONMENT").Rows(27).Resize(Target.Value).Hidden = False
      
      Sheets("INTER APPORTIONMENT").Rows("75:114").Hidden = True
      Sheets("INTER APPORTIONMENT").Rows(75).Resize(Target.Value).Hidden = False
   End If
 
Upvote 0
Solution
Fluff said:
As the 1st part of your code is just looking at C31 how about
VBA Code: 
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "C31" Then
      Rows("39:78").Hidden = True
      Rows(39).Resize(Target.Value).Hidden = False
     
      Sheets("LL Shortfall").Rows("20:59").Hidden = True
      Sheets("LL Shortfall").Rows(20).Resize(Target.Value).Hidden = False
     
      Sheets("INTER APPORTIONMENT").Rows("27:66").Hidden = True
      Sheets("INTER APPORTIONMENT").Rows(27).Resize(Target.Value).Hidden = False
     
      Sheets("INTER APPORTIONMENT").Rows("75:114").Hidden = True
      Sheets("INTER APPORTIONMENT").Rows(75).Resize(Target.Value).Hidden = False
   End If
Click to expand...
this worked thank you, the sheet I was referencing was also password protected so i think this was causing the code not to work
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Kristylee0228
  • Question
Populating Cell based on File Name and using a List
Replies
0
Views
184
Kristylee0228
Kristylee0228
B
  • Solved
Force cursor to go to specific cell in other worksheet
Replies
7
Views
306
bptaw
B
C
  • Solved
VBA Code for hiding columns based on multiple user inputs
2
Replies
14
Views
739
kevin9999
K
H
  • Question
Macro to hide cols C:E is not hiddden
Replies
4
Views
299
mohadin
M
J
  • Question
X400 error code
Replies
1
Views
271
DanteAmor
DanteAmor

Forum statistics

Threads
1,215,026
Messages
6,122,738
Members
449,094
Latest member
dsharae57

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