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.
Any help would be greatly appreciated. Thank you.
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.