Hide a row if cell say "dead" and date is before month in cell?

[tonywatsonhelp]

Hi everyone

So every time I go to a sheet in need a "on activate" macro to hide certain rows

Basically "on activate" if any row/all rows in column G that ="Dead" then if that same row in Column H the date is before the date in Cell D4 hide row

i'm struggling with the date part

Please help

thanks

Tony

 

[My Aswer Is This]

So you always want to look in "Cell D4" for the date

It's not always column D of each row it's always "D4"

Is that what you saying?

 

[tonywatsonhelp]

Hi,
Ok so D4 has a date in it say 01/12/2016

Column H has dates for each row so row
Column G has a word for each row

So for every row that has the word "Dead" in Column G if the date in Column H of that row is before the date in D4 hide the row.

 

[My Aswer Is This]

This is a Module code:
Install this script as you would any Module Script.

Sub Hide_Me()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim ans As Date
Dim Late As Date
ans = Range("D4").Value
Lastrow = Cells(Rows.Count, "G").End(xlUp).Row
For i = 1 To Lastrow
If Cells(i, "G").Value = "Dead" And Cells(i, "H").Value < ans Then Rows(i).Hidden = True
Next
Application.ScreenUpdating = True
End Sub

Then install this script in you sheet this way:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Activate()
Call Hide_Me
End Sub

This will only work for the sheet you install this in.
If you want it in all sheets let me know and I will tell you how to do that.
Now this script only hides the rows if it meets your criteria. It does not unhide rows that are already hidden. You never asked for that. So the row will never unhide with this script if it's hidden already.

 

[tonywatsonhelp]

Thank you My Answer,
works great

Thank you very much

Tony

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Thank you My Answer,
works great

Thank you very much

Tony