Macros for hiding rows based on date

[MVelve]

It seems it should be simple but I have spent a few days on this so am reaching out.  I am just learning Macros and am blown away by what it does, but this one situation is driving me crazy.  I have an Excel doc that the first sheet is entered by Admin Staff, then on the second sheet is the exact same info for 6 columns.  This is the sheet that the Senior staff will be looking at.  What we want on the second sheet is that any row, with a date before "today" in Column F, be hidden so that the Senior Staff only see what is of a concern to them at this point.  We need to keep the info though, that is why there is a sheet one, but on sheet two they will only see staff with a date of today or later.  Also would like to have a macro that unhides these rows.  Would like to set up buttons or toggles so they are just clicked to update the information, but are able to unhide if needed.  i have tried multiple codes and am lost here as why it will not work for me. I know there are other simple ways, but using a button will help as Senior staff only want simple and fast.  Does anyone have suggestions?  Thank you.

[#2]

@MVelve 

With your permission, if I can recommend, It’s helpful to know your operating system and Excel version, as different approaches may be required depending on the version and OS.

Nevertheless,, give this a try (untested but should work)

Private Sub Worksheet_Activate()
Dim i As Long
Dim heute As Date
Sheets("Topics for the next meeting ").Activate
For i = ActiveSheet.UsedRange.Rows.Count to 1 Step -1
Range("A" & i).Select
If ActiveCell.Value > date - 1 Then
ActiveCell.EntireRow.Hidden = True
Else
End If
Next i
End Sub

'Please adjust according to your needs

Thank you for your understanding and patience

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here

[#3]

@NikolinoDE Thank you! Yes it did work except I used it to search in the F Column but it starts searching around row 1100 - Is there a way to have it only search until the last row - which could change as information is added to the main sheet.

[#4]

@MVelve 

It should look for the date and hide or unhide from there.

What if a date in between ends before?

In the end anything is possible, it depends on the effort.

Should it only take the last entered date in F and then work up according to your requirements?

 I don't recommend it, but if that's your wish ... I'll try to prepare it for you (starting tomorrow, after church and after Bistro :).

I think I'll have found the time by the day after tomorrow :)).

To be on the safe side, please describe to me step by step what the Macros should do.

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#5]

@NikolinoDE Thank you! The first worksheet is entered by Admin staff. and needs to be left as is.  The "Summary" sheet is the one we want to update and hide rows.

The dates in column F will be the date that the worksheet "Summary" goes by.  The rows that are "< Date - 1"  in column F will be hidden.  Maybe another value to search would be that the Name has a value in it so that only the rows with a name and the dates will show in the Macro.  What is your recommendation to make this better? A button will be created so when it is pressed, it will update the sheet based on this macro.  What we want the Senior Staff to see are only the names of staff that are still not available for work. Another button would be created to unhide the rows if needed.  Is this enough information from me?

[#6]

about searching around row 1100. Just change Range("A" & i).Select to Range("F2:F100" & i).Select, i think then search only 100 rows. Rest follows :)

[#7]

@NikolinoDE Thank you!  That did not work for me but will keep working on it.  The best way to describe it is in the 2nd worksheet, if Column A2 ( or all of Column A) row 2 (multiple rows) has a value in it (the name) and Column F2 (all of column F2) row 2 (multiple rows) has a date earlier than today (<date) then hide the row.  Would also like to unhide if needed.  Hope this makes sense.

[#8]

@MVelve 

Sry for the late answer, but the pastis in the bistro was very tasty, took a little longer :).

Here is a small suggestion for a solution. As far as tested on the quick, it works.

* I freed the file from useless entries, so it has become much smaller and more manageable.

Hope the proposed solution fits into your project.

File inserted.

Thank you for your patience and time.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.

[#9]

@NikolinoDE Thank you for your work!  It did not work  - it takes away the last few rows and not any of the rows which "14 day return to work" column is before "todays" date.

[#10]

Work with the advanced filters in columns. When I have something I'll send it to you. Thank you for your patience and time. Nikolino

[#11]

If you don't mind me asking, why have the second worksheet that just links to the first worksheet? It creates a very large file that is slow to open (50MB file versus 17KB). Also, if someone inserts a row on your data worksheet, it will be missing from your summary (the newly inserted row will not be linked to your summary worksheet unless you recopy your formulas).

Why not just filter the table on the data entry tab (column k). If you click the drop arrow and select "date filters," there are a lot of standard options (year, month, week) and custom options (before, after, between - with a date picker calendar) available.

I'm having a hard time seeing how the macro is going to save much time over just using the filter - especially as the file grows.