worksheet_change - hiding rows and columns

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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,957
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,957
Office Version
  1. 365
Platform
  1. Windows
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).
 

Ryle23

New Member
Joined
Apr 13, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,957
Office Version
  1. 365
Platform
  1. Windows
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
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.
 

Ryle23

New Member
Joined
Apr 13, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows

Ryle23

New Member
Joined
Apr 13, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows

Watch MrExcel Video

Forum statistics

Threads
1,129,810
Messages
5,638,483
Members
417,027
Latest member
wlknspc7

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
Top