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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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

I
  • Question Question
VBA to detect hidden rows. then Re-hide
Replies
7
Views
160
Skyybot
Skyybot
D
  • Question Question
Hide Rows Based on Another Sheets Values
Replies
9
Views
309
Joe4
Joe4
E
  • Solved
Quick VBA code to Hide Rows based on Cell Value
Replies
3
Views
589
Joe4
Joe4
O
  • Question Question
Somewhat basic UDFs work except that they return #VALUE! error after hiding rows with VBA.
Replies
2
Views
129
OaklandJim
O
M
  • Question Question
VBA Macro - hides columns based on a single cell which is limited by data validation
Replies
4
Views
352
macca_18380
M

Forum statistics

Threads
1,224,521
Messages
6,179,290
Members
452,902
Latest member
Knuddeluff

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