Hide rows based on Datediff

[Blanchetdb]

I need some help combining both. I have two cells where dates are entered, so if the difference between both dates is greater than 6 months then rows 71:73 unhide......

 

[MARK858]

I assume you meant 2018-04-15 rather than 2108-04-15 anyway try...

Sub Macro3()
    If Range("K68") > DateSerial(Year(Range("H68")), Month(Range("H68")) + 6, Day(Range("H68"))) Then
        Worksheets("Sheet1").Range("73:75").EntireRow.Hidden = True
    Else
        Worksheets("Sheet1").Range("73:75").EntireRow.Hidden = False
    End If
End Sub

 

[Trebor76]

Here's my attempt (though I'd probably use MARK858's to be honest ):

Option Explicit
Sub Macro3()

    Dim intNumOfDays As Integer
    Dim intNumOfMths As Integer
    Dim wsMySheet As Worksheet
    
    Set wsMySheet = ThisWorkbook.Sheets("Sheet1")
    
    intNumOfDays = Day(wsMySheet.Range("K68")) - Day(wsMySheet.Range("H68"))
    intNumOfMths = Evaluate("DATEDIF(" & CStr(wsMySheet.Range("H68").Address) & "," & CStr(wsMySheet.Range("K68").Address) & ",""M"")")

    With wsMySheet
        If intNumOfMths > 6 Then
            .Range("73:75").EntireRow.Hidden = True
        ElseIf intNumOfMths = 6 And intNumOfDays >= 0 Then
            .Range("73:75").EntireRow.Hidden = True
        Else
            .Range("73:75").EntireRow.Hidden = False
        End If
    End With
    
    Set wsMySheet = Nothing
    
End Sub

Hey MARK858,

Congrats on just passing your 10,000th post. Well done!!

Robert

 

[MARK858]

Congrats on just passing your 10,000th post. Well done!!

Didn't notice I had Thanks for pointing it out

 

[Blanchetdb]

I got it to work thanks to you but now it only works after I "run" the macro.....I need it to happen automatically

this is what I have:

Sub Priority ()

If Range("K68") > DateSerial(Year(Range("H68")), Month(Range("H68")) + 6, Day(Range("H68"))) Then
Worksheets("Sheet1").Range("72:74").EntireRow.Hidden = False
Else
Worksheets("Sheet1").Range("72:74").EntireRow.Hidden = True
End If

If Range("K68") < DateSerial(Year(Range("H68")), Month(Range("H68")) + 6, Day(Range("H68"))) Then
Worksheets("Sheet1").Range("75:76").EntireRow.Hidden = False
Else
Worksheets("Sheet1").Range("75:76").EntireRow.Hidden = True
End If

End Sub

Why isn't it triggered as soon the dates are entered?

 

[ChrisRamsden]

Have you put the code in the "View Code" section of the sheet tab. Right click on the tab, click view code and put the code here. It should then trigger everytime the cells referenced are changed.

Thanks,

Chris

 

[MARK858]

Why isn't it triggered as soon the dates are entered?

Because you didn't state it needed to and to do it properly it requires a total rewrite of the code. If I get time I will have a look at it when I knock off work