Forum Discussion
Macros for hiding rows based on date
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.
10 Replies
- 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. - 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
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 needsThank 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
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.
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)
