Hide Rows and Column in multiple sheet based on a cell value of another sheet

L

lemmorsojedirdam16

New Member
Joined
Mar 16, 2018
Messages
13
Hi All... I am new to vba and still wanted to learn. I need your help to solve my problem. I would like to know how I am able to add more task on below vba to hide rows and columns of multiple sheet based on a cell value of another sheet.

Let say: On Sheet1, If I enter a value of 7 (this is number of days) and etc on cell B8, the rows 10 to 20 of sheet 2, sheet 3, sheet 4 and so on will be hidden and the columns AB:CF of sheet1 will be hidden.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim aNames
  Dim i As Long
  
  Const sNames As String = "Sheet2, Sheet3, Sheet4, Sheet5"   '<- Names of your sheets (can add more)
  Const RowCell As String = "B8"              '<- Cell you enter no. of rows into
  
  If Target.Address(0, 0) = RowCell Then
    aNames = Split(sNames, ", ")
    Application.ScreenUpdating = False
    For i = 0 To UBound(aNames)
      With Sheets(aNames(i))
         .Rows("44:115").Hidden = False
         .Columns("AE:CF").Hidden = False
        If Target.Value = "7" Then
          .Rows("44:115").Hidden = True
           .Columns("AE:CF").Hidden = True
        End If

      .Columns("S:CF").Hidden = False 
    .Rows("28:115").Hidden = False
        If Target.Value = "3" Then
          .Rows("28:115").Hidden = True
.Columns("S:CF").Hidden = True
        End If 
  
.Columns("AK:CF").Hidden = False
    .Rows("52:115").Hidden = False
        If Target.Value = "9" Then
          .Rows("52:115").Hidden = True
.Columns("AK:CF").Hidden = True
        End If       

                                                         ' <--- I can add more similar task
        
      End With
    Next i
    Application.ScreenUpdating = True
  End If
End Sub

I hope someone will help me. Thanks in advance
 
Last edited by a moderator:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Hi & welcome to MrExcel.
Is this what you want
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim aNames
   Dim i As Long
   
   Const sNames As String = "Sheet2, Sheet3, Sheet4, Sheet5"   '<- Names of your sheets (can add more)
   If Target.CountLarge > 1 Then Exit Sub
   
   If Target = Range("B8") Then
      aNames = Split(sNames, ", ")
      Application.ScreenUpdating = False
      For i = 0 To UBound(aNames)
         With Sheets(aNames(i))
            .Rows("28:115").Hidden = False
            .Columns("S:CF").Hidden = False
            Select Case Target.Value
               Case 7
                  .Range("10:20,44:115").EntireRow.Hidden = True
                  .Columns("AE:CF").Hidden = True
               Case 3
                  .Rows("28:115").Hidden = True
                  .Columns("S:CF").Hidden = True
               Case 9
                  .Rows("52:115").Hidden = True
                  .Columns("AK:CF").Hidden = True
            End Select
         End With
      Next i
      Application.ScreenUpdating = True
   End If
End Sub
 
Upvote 0
[SOLVED] Re: Hide Rows and Column in multiple sheet based on a cell value of another sheet

Fluff said:
Hi & welcome to MrExcel.
Is this what you want
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim aNames
   Dim i As Long
   
   Const sNames As String = "Sheet2, Sheet3, Sheet4, Sheet5"   '<- Names of your sheets (can add more)
   If Target.CountLarge > 1 Then Exit Sub
   
   If Target = Range("B8") Then
      aNames = Split(sNames, ", ")
      Application.ScreenUpdating = False
      For i = 0 To UBound(aNames)
         With Sheets(aNames(i))
            .Rows("28:115").Hidden = False
            .Columns("S:CF").Hidden = False
            Select Case Target.Value
               Case 7
                  .Range("10:20,44:115").EntireRow.Hidden = True
                  .Columns("AE:CF").Hidden = True
               Case 3
                  .Rows("28:115").Hidden = True
                  .Columns("S:CF").Hidden = True
               Case 9
                  .Rows("52:115").Hidden = True
                  .Columns("AK:CF").Hidden = True
            End Select
         End With
      Next i
      Application.ScreenUpdating = True
   End If
End Sub
Click to expand...



Thanks a lot Fluff for your help... Yes, this is the one I want. I hope I will learn more from this forum.
Big Thanks @Fluff
 
Upvote 0
Fluff said:
Hi & welcome to MrExcel.
Is this what you want
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim aNames
   Dim i As Long
   
   Const sNames As String = "Sheet2, Sheet3, Sheet4, Sheet5"   '<- Names of your sheets (can add more)
   If Target.CountLarge > 1 Then Exit Sub
   
   If Target = Range("B8") Then
      aNames = Split(sNames, ", ")
      Application.ScreenUpdating = False
      For i = 0 To UBound(aNames)
         With Sheets(aNames(i))
            .Rows("28:115").Hidden = False
            .Columns("S:CF").Hidden = False
            Select Case Target.Value
               Case 7
                  .Range("10:20,44:115").EntireRow.Hidden = True
                  .Columns("AE:CF").Hidden = True
               Case 3
                  .Rows("28:115").Hidden = True
                  .Columns("S:CF").Hidden = True
               Case 9
                  .Rows("52:115").Hidden = True
                  .Columns("AK:CF").Hidden = True
            End Select
         End With
      Next i
      Application.ScreenUpdating = True
   End If
End Sub
Click to expand...

@Fluff
I have 1 more question... hiding rows for sheets 1, 2 3, 4 and 5 are okay with this formula. But the column should be hiding on sheet 1 not on sheets 1, 2, 3, 4 and 5.
 
Upvote 0
In that case try
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim aNames
   Dim i As Long
   
   Const sNames As String = "Sheet2, Sheet3, Sheet4, Sheet5"   '<- Names of your sheets (can add more)
   If Target.CountLarge > 1 Then Exit Sub
   
   If Target = Range("B8") Then
      aNames = Split(sNames, ", ")
      Application.ScreenUpdating = False
      For i = 0 To UBound(aNames)
         With Sheets(aNames(i))
            .Rows("28:115").Hidden = False
            Sheets("sheet1").Columns("S:CF").Hidden = False
            Select Case Target.Value
               Case 7
                  .Range("10:20,44:115").EntireRow.Hidden = True
                   Sheets("sheet1").Columns("AE:CF").Hidden = True
               Case 3
                  .Rows("28:115").Hidden = True
                   Sheets("sheet1").Columns("S:CF").Hidden = True
               Case 9
                  .Rows("52:115").Hidden = True
                   Sheets("sheet1").Columns("AK:CF").Hidden = True
            End Select
         End With
      Next i
      Application.ScreenUpdating = True
   End If
End Sub
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
509
kevin9999
K
N
  • Question
Hiding rows based on cell value
Replies
2
Views
180
smozgur
smozgur
R
  • Question
How to combine the functions of having the ability to select multiple items from a drop down list and hide columns based on values of said list?
Replies
0
Views
234
rgree05
R
A
  • Question
VBA - Hide range of rows IF
Replies
3
Views
113
Osvaldo Palmeiro
O
E
  • Question
Hide rows based on prior setup sheet
2
Replies
10
Views
245
edjohn20
E

Forum statistics

Threads
1,215,529
Messages
6,125,344
Members
449,219
Latest member
Smiqer

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