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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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

E
  • Solved
Quick VBA code to Hide Rows based on Cell Value
Replies
3
Views
426
Joe4
Joe4
M
  • Question Question
VBA Macro - hides columns based on a single cell which is limited by data validation
Replies
4
Views
298
macca_18380
M
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
1K
kevin9999
K
M
  • Question Question
VBA Macro - hides rows based on a single cell which is limited by data validation
Replies
6
Views
358
My Aswer Is This
My Aswer Is This
N
  • Question Question
Hiding/showing rows between two values
Replies
1
Views
406
Joe4
Joe4

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

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