Hide/Unhide Rows Based on date on another sheet

james7705

New Member
Joined
Dec 7, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hello
I'm looking to hide rows based on a date value on another sheet. I have a date on "Index!C1" formatted to dd/mm/yyyy.
On "Sheet2" through to "Sheet26" I have column B9 to B39 populated with numbers from 1 to 31.
Column A9 to A39 is formatted to show the day as "dddd" referencing column B and the month and year from "Index!C1" with this formula (=DATE(B7;A7;B9) A7 being equal to "Index!C1"
I would like to hide the last row (39) on all the sheets if the month only has 30 days or the last 3 rows if it is February.
This is the code I'm using, but it is not working and I'm not sure what I am doing as I am very new to vba.

VBA Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim x As Long

If Not Intersect(Target, Range("Index!C1")) Is Nothing Then
    x = Day(WorksheetFunction.EoMonth(Range("Index!C1").Value, 0))
    Range("B:B").Resize(rowsize:=x).EntireRow.Hidden = False
    If x < 31 Then
        Range("B9:B39").Offset(0, x - 28).Resize(rowsize:=31 - x).EntireRow.Hidden = True
    End If
End If

Any help would be greatly appreciated. Thank you.
 

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.

eirikdaude

Board Regular
Joined
Nov 26, 2013
Messages
60
I assume your code is located in the worksheet code of your Index-sheet?

In that case I think the following would work for you. Note that it is a bit more verbose than the code you have, but I think it is easier to understand the logic in it:

VBA Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Dim date_cell As Range
    Dim days_in_month As Long
    Dim i As Long
   
    Set date_cell = Me.Range("C1")
   
    If Not Intersect(Target, date_cell) Is Nothing Then
        days_in_month = Day(WorksheetFunction.EoMonth(date_cell, 0))
       
        For i = 2 To 26
            With ThisWorkbook.Worksheets("Sheet" & CStr(i))
                .Rows("37:39").Hidden = False
                Select Case days_in_month
                    Case 28:
                        .Rows("37:39").Hidden = True
                    Case 29:
                        .Rows("38:39").Hidden = True
                    Case 30:
                        .Rows(39).Hidden = True
                    Case Else:
                End Select
            End With
        Next i
    End If
End Sub

The obvious problem with the code you had is that it most likely only hid the rows in your index sheet.

Note that when working with events like Worksheet_Change it might be a good idea to turn Application.EnableEvents to False before executing your code and then turning it back to True once the code has finished running. It won't create a problem in this case, but if you e.g. did something in your macro which led to a change in Index!C1 you could very easily get an infinite loop.
 
Last edited:

james7705

New Member
Joined
Dec 7, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hi eirikdaude

So I tried the code above and it is giving me a Run-time error '9':
"Subscript out of range"
I'm not sure what that means?
 

eirikdaude

Board Regular
Joined
Nov 26, 2013
Messages
60
Hi eirikdaude

So I tried the code above and it is giving me a Run-time error '9':
"Subscript out of range"
I'm not sure what that means?
I am guessing it is stopping on the line "With ThisWorkbook.Worksheets("Sheet" & CStr(i))", and the reason for the error is most likely that you don't have sheets named all the way from sheet2 to sheet26 in your workbook. In other words you get an error because the code tries to access a sheet which doesn't exist.

If the error isn't on that line (or even if it is), please provide more details and / or a screenshot to make it easier to troubleshoot what might be wrong.
 

james7705

New Member
Joined
Dec 7, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Yes. That is where it stops. Is there any way to get around that as the amount of sheets changes depending on the department using the spreadsheet. Some departments use less sheets and some use more to a max of 26
 

james7705

New Member
Joined
Dec 7, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
It gives me the same runtime error even if I change the values of i to correspond to the amount of sheets
 

eirikdaude

Board Regular
Joined
Nov 26, 2013
Messages
60

ADVERTISEMENT

Are the names of the sheets always "Sheet2", "Sheet3" etc?

You can change the from and to values of the for loop, but that will still depend on this being the naming scheme.
 

james7705

New Member
Joined
Dec 7, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
No. Unfortunately not. Users change the sheet names based on the name of the employee.
Some background...
Each sheet tracks attendance for each employee per department.
Sheet1 is the Index with a list of the employee names and other info
Sheets2 to a max of 26 are for tracking attendance and overtime for each employee.
Sheet27 is a summary report of all employees hours
In the vba I have the tree on the left which shows the sheet names i.e Sheet1(Index), Sheet2("Employee1"), Sheet3("Employee2"), Sheet4("Employee3"),...Sheet27("Exception Report")
 

eirikdaude

Board Regular
Joined
Nov 26, 2013
Messages
60
No. Unfortunately not. Users change the sheet names based on the name of the employee.
Some background...
Each sheet tracks attendance for each employee per department.
Sheet1 is the Index with a list of the employee names and other info
Sheets2 to a max of 26 are for tracking attendance and overtime for each employee.
Sheet27 is a summary report of all employees hours
In the vba I have the tree on the left which shows the sheet names i.e Sheet1(Index), Sheet2("Employee1"), Sheet3("Employee2"), Sheet4("Employee3"),...Sheet27("Exception Report")
I'd recommend working with the codenames of the sheets in that case. Can you ensure that each sheet containing employee data e.g. has a codename like "employee1"? In that case you can just make the loop

VBA Code:
For Each ws in Thisworkbook.Worksheets
    If InStr(1, StrConv(ws.CodeName, vbLowerCase), "employee", vbTextCompare) > 0 Then
        ' The code to hide the rows
    End If
Next ws

You could possibly do something like

VBA Code:
if strconv(ws.codename, vblowercase) = "sheet" & cstr(i) then
' code to hide rows
end if

In the code I already gave you, without changing the codenames, but this will be much more prone to failure
 

james7705

New Member
Joined
Dec 7, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
I don't think that will work as the sheet names get changed to Employee name e.g. "John Smith".
Excuse my ignorance here, but would it not be easier to loop through all the sheets except the sheet named "Index" and the sheet named "Exception Report" as all the other sheets are exactly the same and i would like this code to run on all of them except as I said, sheet "Index" and sheet "Exception Report"?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,163
Messages
5,640,510
Members
417,148
Latest member
pe3087te

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