worksheet_change - hiding rows and columns

B

boydcaroline73

New Member
Joined
Mar 29, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi all

I am trying to get 2 changes onto the same sheet. So I put a number into cell B2 and this hides/unhides rows based on that number. This code below works fine. I now need to add in a code to hide columns based on a number put into cell B3. eg 1 hides columns C and D, and 2 will just hide column D. Any help greatly appreciated.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("B2"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "1": Rows("38:297").EntireRow.Hidden = True
                Rows("14:37").EntireRow.Hidden = False
Case Is = "2": Rows("14:63").EntireRow.Hidden = False
                Rows("66:297").EntireRow.Hidden = True
Case Is = "3": Rows("14:89").EntireRow.Hidden = False
                Rows("92:297").EntireRow.Hidden = True
Case Is = "4": Rows("14:115").EntireRow.Hidden = False
                Rows("118:297").EntireRow.Hidden = True
Case Is = "5": Rows("14:141").EntireRow.Hidden = False
                Rows("144:297").EntireRow.Hidden = True
Case Is = "6": Rows("14:167").EntireRow.Hidden = False
                Rows("170:297").EntireRow.Hidden = True
Case Is = "7": Rows("14:193").EntireRow.Hidden = False
                Rows("196:297").EntireRow.Hidden = True
Case Is = "8": Rows("14:219").EntireRow.Hidden = False
                Rows("222:297").EntireRow.Hidden = True
Case Is = "9": Rows("14:245").EntireRow.Hidden = False
                Rows("248:297").EntireRow.Hidden = True
Case Is = "10": Rows("14:271").EntireRow.Hidden = False
                Rows("274:297").EntireRow.Hidden = True
Case Is = "11": Rows("14:297").EntireRow.Hidden = False
                End Select
                End If
                End Sub
 
Last edited by a moderator:

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
Welcome to the Board!

Just add another If block checking those conditions.
But first, a little housekeeping:

1. This line is unnecessary:
VBA Code: 
ActiveSheet.Activate
There is no need to activate the Active sheet. It is already active!
And Worksheet_Change event procedures only run against the sheet module that they are placed in.

2. This:
VBA Code: 
Range(Target.Address)
can just be simplified to this:
VBA Code: 
Target
as "Target" is already a range variable, by default, in Worksheet_Change event procedures.

3. The "EntireRow" part of this line is unnecessary:
VBA Code: 
Rows("38:297").EntireRow.Hidden = True
since you are already specifying rows. Just use:
VBA Code: 
Rows("38:297").Hidden = True

So here is the updated code with the addition you wanted:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

'   Check cell B2 to hide rows
    If Not Application.Intersect(Range("B2"), Target) Is Nothing Then
        Select Case Target.Value
            Case Is = "1": Rows("38:297").Hidden = True
                Rows("14:37").Hidden = False
            Case Is = "2": Rows("14:63").Hidden = False
                Rows("66:297").Hidden = True
            Case Is = "3": Rows("14:89").Hidden = False
                Rows("92:297").Hidden = True
            Case Is = "4": Rows("14:115").Hidden = False
                Rows("118:297").Hidden = True
            Case Is = "5": Rows("14:141").Hidden = False
                Rows("144:297").Hidden = True
            Case Is = "6": Rows("14:167").Hidden = False
                Rows("170:297").Hidden = True
            Case Is = "7": Rows("14:193").Hidden = False
                Rows("196:297").Hidden = True
            Case Is = "8": Rows("14:219").Hidden = False
                Rows("222:297").Hidden = True
            Case Is = "9": Rows("14:245").Hidden = False
                Rows("248:297").Hidden = True
            Case Is = "10": Rows("14:271").Hidden = False
                Rows("274:297").Hidden = True
            Case Is = "11": Rows("14:297").Hidden = False
        End Select
    End If
              
'   Check cell B3 to hide rows
    If Not Application.Intersect(Range("B3"), Target) Is Nothing Then
        Select Case Target.Value
            Case Is = "1": Columns("C:D").Hidden = True
            Case Is = "2": Columns("D:D").Hidden = True
                Columns("C:C").Hidden = False
        End Select
    End If
  
End Sub
 
Upvote 0
Solution
You are welcome.
Glad I was able to help!

Yes, there is a lot to learn with VBA.
I hope my explanations make sense. If you have any questions about them, please let me know. I will be glad to explain (to help in your understanding).
 
Upvote 0
Good day. I had this kind of code also. but I wanna add an effect on other sheets also.

this is my present code..

the sheet on where the cell update is on "Masterlist" sheet.. and I was hoping I can also hide rows on "Attedance" sheet, "Raw Scores" sheet and ""Final Ratings" sheet if the cell in "Masterlist" sheet is updated.

the range of rows that i want hidden would be

on Attendance sheet ---> rows 8 to 57 (50 rows)
on Raw Scores and Final Ratings sheet ---> rows 21 to 70 (50 rows)

--------------------------------------------------------------------------------------------------------
(This code is written under "Masterlist" Sheet")

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Unprotect Password:="password"

If Not Application.Intersect(Range("J4"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value

Case Is = "50": Rows("12:61").EntireRow.Hidden = False

Case Is = "49": Rows("12:60").EntireRow.Hidden = False
Rows("61").EntireRow.Hidden = True

Case Is = "48": Rows("12:59").EntireRow.Hidden = False
Rows("60:61").EntireRow.Hidden = True

Case Is = "47": Rows("12:58").EntireRow.Hidden = False
Rows("59:61").EntireRow.Hidden = True

Case Is = "46": Rows("12:57").EntireRow.Hidden = False
Rows("58:61").EntireRow.Hidden = True

Case Is = "45": Rows("12:56").EntireRow.Hidden = False
Rows("57:61").EntireRow.Hidden = True

Case Is = "44": Rows("12:55").EntireRow.Hidden = False
Rows("56:61").EntireRow.Hidden = True

Case Is = "43": Rows("12:54").EntireRow.Hidden = False
Rows("55:61").EntireRow.Hidden = True

Case Is = "42": Rows("12:53").EntireRow.Hidden = False
Rows("54:61").EntireRow.Hidden = True

Case Is = "41": Rows("12:52").EntireRow.Hidden = False
Rows("53:61").EntireRow.Hidden = True

Case Is = "40": Rows("12:51").EntireRow.Hidden = False
Rows("52:61").EntireRow.Hidden = True

Case Is = "39": Rows("12:50").EntireRow.Hidden = False
Rows("51:61").EntireRow.Hidden = True

Case Is = "38": Rows("12:49").EntireRow.Hidden = False
Rows("50:61").EntireRow.Hidden = True

Case Is = "37": Rows("12:48").EntireRow.Hidden = False
Rows("49:61").EntireRow.Hidden = True

Case Is = "36": Rows("12:47").EntireRow.Hidden = False
Rows("48:61").EntireRow.Hidden = True

Case Is = "35": Rows("12:46").EntireRow.Hidden = False
Rows("47:61").EntireRow.Hidden = True

Case Is = "34": Rows("12:45").EntireRow.Hidden = False
Rows("46:61").EntireRow.Hidden = True

Case Is = "33": Rows("12:44").EntireRow.Hidden = False
Rows("45:61").EntireRow.Hidden = True

Case Is = "32": Rows("12:43").EntireRow.Hidden = False
Rows("44:61").EntireRow.Hidden = True

Case Is = "31": Rows("12:42").EntireRow.Hidden = False
Rows("43:61").EntireRow.Hidden = True

Case Is = "30": Rows("12:41").EntireRow.Hidden = False
Rows("42:61").EntireRow.Hidden = True

Case Is = "29": Rows("12:40").EntireRow.Hidden = False
Rows("41:61").EntireRow.Hidden = True

Case Is = "28": Rows("12:39").EntireRow.Hidden = False
Rows("40:61").EntireRow.Hidden = True

Case Is = "27": Rows("12:38").EntireRow.Hidden = False
Rows("39:61").EntireRow.Hidden = True

Case Is = "26": Rows("12:37").EntireRow.Hidden = False
Rows("38:61").EntireRow.Hidden = True

Case Is = "25": Rows("12:36").EntireRow.Hidden = False
Rows("37:61").EntireRow.Hidden = True

Case Is = "24": Rows("12:35").EntireRow.Hidden = False
Rows("36:61").EntireRow.Hidden = True

Case Is = "23": Rows("12:34").EntireRow.Hidden = False
Rows("35:61").EntireRow.Hidden = True

Case Is = "22": Rows("12:33").EntireRow.Hidden = False
Rows("34:61").EntireRow.Hidden = True

Case Is = "21": Rows("12:32").EntireRow.Hidden = False
Rows("33:61").EntireRow.Hidden = True

Case Is = "20": Rows("12:31").EntireRow.Hidden = False
Rows("32:61").EntireRow.Hidden = True

Case Is = "19": Rows("12:30").EntireRow.Hidden = False
Rows("31:61").EntireRow.Hidden = True

Case Is = "18": Rows("12:29").EntireRow.Hidden = False
Rows("30:61").EntireRow.Hidden = True

Case Is = "17": Rows("12:28").EntireRow.Hidden = False
Rows("29:61").EntireRow.Hidden = True

Case Is = "16": Rows("12:27").EntireRow.Hidden = False
Rows("28:61").EntireRow.Hidden = True

Case Is = "15": Rows("12:26").EntireRow.Hidden = False
Rows("27:61").EntireRow.Hidden = True

Case Is = "14": Rows("12:25").EntireRow.Hidden = False
Rows("26:61").EntireRow.Hidden = True

Case Is = "13": Rows("12:24").EntireRow.Hidden = False
Rows("25:61").EntireRow.Hidden = True

Case Is = "12": Rows("12:23").EntireRow.Hidden = False
Rows("24:61").EntireRow.Hidden = True

Case Is = "11": Rows("12:22").EntireRow.Hidden = False
Rows("23:61").EntireRow.Hidden = True

Case Is = "10": Rows("12:21").EntireRow.Hidden = False
Rows("22:61").EntireRow.Hidden = True

Case Is = "9": Rows("12:20").EntireRow.Hidden = False
Rows("21:61").EntireRow.Hidden = True

Case Is = "8": Rows("12:19").EntireRow.Hidden = False
Rows("20:61").EntireRow.Hidden = True

Case Is = "7": Rows("12:18").EntireRow.Hidden = False
Rows("19:61").EntireRow.Hidden = True

Case Is = "6": Rows("12:17").EntireRow.Hidden = False
Rows("18:61").EntireRow.Hidden = True

Case Is = "5": Rows("12:16").EntireRow.Hidden = False
Rows("17:61").EntireRow.Hidden = True

Case Is = "4": Rows("12:15").EntireRow.Hidden = False
Rows("16:61").EntireRow.Hidden = True

Case Is = "3": Rows("12:14").EntireRow.Hidden = False
Rows("15:61").EntireRow.Hidden = True

Case Is = "2": Rows("12:13").EntireRow.Hidden = False
Rows("14:61").EntireRow.Hidden = True

Case Is = "1": Rows("12").EntireRow.Hidden = False
Rows("13:61").EntireRow.Hidden = True

End Select

End If

ActiveSheet.Protect Password:="password"

End Sub
 
Upvote 0
Ryle23 said:
Good day. I had this kind of code also. but I wanna add an effect on other sheets also.

this is my present code..

the sheet on where the cell update is on "Masterlist" sheet.. and I was hoping I can also hide rows on "Attedance" sheet, "Raw Scores" sheet and ""Final Ratings" sheet if the cell in "Masterlist" sheet is updated.

the range of rows that i want hidden would be

on Attendance sheet ---> rows 8 to 57 (50 rows)
on Raw Scores and Final Ratings sheet ---> rows 21 to 70 (50 rows)

--------------------------------------------------------------------------------------------------------
(This code is written under "Masterlist" Sheet")

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Unprotect Password:="password"

If Not Application.Intersect(Range("J4"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value

Case Is = "50": Rows("12:61").EntireRow.Hidden = False

Case Is = "49": Rows("12:60").EntireRow.Hidden = False
Rows("61").EntireRow.Hidden = True

Case Is = "48": Rows("12:59").EntireRow.Hidden = False
Rows("60:61").EntireRow.Hidden = True

Case Is = "47": Rows("12:58").EntireRow.Hidden = False
Rows("59:61").EntireRow.Hidden = True

Case Is = "46": Rows("12:57").EntireRow.Hidden = False
Rows("58:61").EntireRow.Hidden = True

Case Is = "45": Rows("12:56").EntireRow.Hidden = False
Rows("57:61").EntireRow.Hidden = True

Case Is = "44": Rows("12:55").EntireRow.Hidden = False
Rows("56:61").EntireRow.Hidden = True

Case Is = "43": Rows("12:54").EntireRow.Hidden = False
Rows("55:61").EntireRow.Hidden = True

Case Is = "42": Rows("12:53").EntireRow.Hidden = False
Rows("54:61").EntireRow.Hidden = True

Case Is = "41": Rows("12:52").EntireRow.Hidden = False
Rows("53:61").EntireRow.Hidden = True

Case Is = "40": Rows("12:51").EntireRow.Hidden = False
Rows("52:61").EntireRow.Hidden = True

Case Is = "39": Rows("12:50").EntireRow.Hidden = False
Rows("51:61").EntireRow.Hidden = True

Case Is = "38": Rows("12:49").EntireRow.Hidden = False
Rows("50:61").EntireRow.Hidden = True

Case Is = "37": Rows("12:48").EntireRow.Hidden = False
Rows("49:61").EntireRow.Hidden = True

Case Is = "36": Rows("12:47").EntireRow.Hidden = False
Rows("48:61").EntireRow.Hidden = True

Case Is = "35": Rows("12:46").EntireRow.Hidden = False
Rows("47:61").EntireRow.Hidden = True

Case Is = "34": Rows("12:45").EntireRow.Hidden = False
Rows("46:61").EntireRow.Hidden = True

Case Is = "33": Rows("12:44").EntireRow.Hidden = False
Rows("45:61").EntireRow.Hidden = True

Case Is = "32": Rows("12:43").EntireRow.Hidden = False
Rows("44:61").EntireRow.Hidden = True

Case Is = "31": Rows("12:42").EntireRow.Hidden = False
Rows("43:61").EntireRow.Hidden = True

Case Is = "30": Rows("12:41").EntireRow.Hidden = False
Rows("42:61").EntireRow.Hidden = True

Case Is = "29": Rows("12:40").EntireRow.Hidden = False
Rows("41:61").EntireRow.Hidden = True

Case Is = "28": Rows("12:39").EntireRow.Hidden = False
Rows("40:61").EntireRow.Hidden = True

Case Is = "27": Rows("12:38").EntireRow.Hidden = False
Rows("39:61").EntireRow.Hidden = True

Case Is = "26": Rows("12:37").EntireRow.Hidden = False
Rows("38:61").EntireRow.Hidden = True

Case Is = "25": Rows("12:36").EntireRow.Hidden = False
Rows("37:61").EntireRow.Hidden = True

Case Is = "24": Rows("12:35").EntireRow.Hidden = False
Rows("36:61").EntireRow.Hidden = True

Case Is = "23": Rows("12:34").EntireRow.Hidden = False
Rows("35:61").EntireRow.Hidden = True

Case Is = "22": Rows("12:33").EntireRow.Hidden = False
Rows("34:61").EntireRow.Hidden = True

Case Is = "21": Rows("12:32").EntireRow.Hidden = False
Rows("33:61").EntireRow.Hidden = True

Case Is = "20": Rows("12:31").EntireRow.Hidden = False
Rows("32:61").EntireRow.Hidden = True

Case Is = "19": Rows("12:30").EntireRow.Hidden = False
Rows("31:61").EntireRow.Hidden = True

Case Is = "18": Rows("12:29").EntireRow.Hidden = False
Rows("30:61").EntireRow.Hidden = True

Case Is = "17": Rows("12:28").EntireRow.Hidden = False
Rows("29:61").EntireRow.Hidden = True

Case Is = "16": Rows("12:27").EntireRow.Hidden = False
Rows("28:61").EntireRow.Hidden = True

Case Is = "15": Rows("12:26").EntireRow.Hidden = False
Rows("27:61").EntireRow.Hidden = True

Case Is = "14": Rows("12:25").EntireRow.Hidden = False
Rows("26:61").EntireRow.Hidden = True

Case Is = "13": Rows("12:24").EntireRow.Hidden = False
Rows("25:61").EntireRow.Hidden = True

Case Is = "12": Rows("12:23").EntireRow.Hidden = False
Rows("24:61").EntireRow.Hidden = True

Case Is = "11": Rows("12:22").EntireRow.Hidden = False
Rows("23:61").EntireRow.Hidden = True

Case Is = "10": Rows("12:21").EntireRow.Hidden = False
Rows("22:61").EntireRow.Hidden = True

Case Is = "9": Rows("12:20").EntireRow.Hidden = False
Rows("21:61").EntireRow.Hidden = True

Case Is = "8": Rows("12:19").EntireRow.Hidden = False
Rows("20:61").EntireRow.Hidden = True

Case Is = "7": Rows("12:18").EntireRow.Hidden = False
Rows("19:61").EntireRow.Hidden = True

Case Is = "6": Rows("12:17").EntireRow.Hidden = False
Rows("18:61").EntireRow.Hidden = True

Case Is = "5": Rows("12:16").EntireRow.Hidden = False
Rows("17:61").EntireRow.Hidden = True

Case Is = "4": Rows("12:15").EntireRow.Hidden = False
Rows("16:61").EntireRow.Hidden = True

Case Is = "3": Rows("12:14").EntireRow.Hidden = False
Rows("15:61").EntireRow.Hidden = True

Case Is = "2": Rows("12:13").EntireRow.Hidden = False
Rows("14:61").EntireRow.Hidden = True

Case Is = "1": Rows("12").EntireRow.Hidden = False
Rows("13:61").EntireRow.Hidden = True

End Select

End If

ActiveSheet.Protect Password:="password"

End Sub
Click to expand...
This new question should be its own thread, which I see you posted here: Hide/Unhide Rows on different sheets based on cell value from one main sheet

Note: I see that you marked your other thread as "Solved". Not sure if you intended to do that, but that tells people that it has ben solved and there is no reason for them to look at it or reply.
 
Upvote 0
Upvote 0
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Hide Rows based on cell with drop down values
Replies
1
Views
374
kevin9999
K
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
378
kevin9999
K
A
  • Question
Using a cell on another worksheet to run my code?
Replies
1
Views
264
rlv01
R
J
  • Question
VBA Macro for hiding and unhiding rows is rearranging cells
2
Replies
14
Views
919
dmt32
D
W
  • Solved
Macro slowing down excessivly
Replies
4
Views
377
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,522
Messages
6,120,022
Members
448,939
Latest member
Leon Leenders

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