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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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 Question
Populating Cell based on File Name and using a List
Replies
0
Views
416
Kristylee0228
Kristylee0228
B
  • Solved
Force cursor to go to specific cell in other worksheet
Replies
7
Views
701
bptaw
B
excelbytes
  • Solved
Looping Procedure In VBA Code
2 3
Replies
24
Views
437
excelbytes
excelbytes
C
  • Solved
VBA Code for hiding columns based on multiple user inputs
2
Replies
14
Views
1K
kevin9999
K
T
  • Solved
Help with VBA Code in Excel
Replies
2
Views
294
tixarah
T

Forum statistics

Threads
1,223,609
Messages
6,173,331
Members
452,510
Latest member
RCan29

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