Help Needed: Hide/Unhide rows of Terminated/rehired employees based on date cell value

[Life_is]

Hi All, hoping for some help. Our business has high and low employment demand depending on the number and size of projects, as a result we terminate and rehire many of the same employees over time. Additionally, we offer 3 different types of PTO tracked in 3 separate workbooks, so adding, removing and re-adding the same employees gets time consuming. I would like to hide a row based on a date being entered in the termination date column D and unhide the row when the employee is rehired. If possible, to unhide terminated employee enter their name in C1; to temporarily unhide row then clear the termination date and enter the rehire date in Column E to keep row unhidden; if they are terminated/rehired at a later date the same action repeats, except then the rehire date would, be cleared the next time the same employee is terminated. I would, also, note that the row range will grow over time.

I have tried Advanced Filters, but this action to hide then unhide rows is not efficient and leaves room for too many errors where multiple users are involved.

I'm not well versed in VBA, but see that it is, likely, my only option; it will be the only VBA in the workbook. If what I'm asking is not a viable option, what would be the best solution? Any help is greatly appreciated. 

Thank you,

Shawn

[#2]

@Life_is 

Creating a VBA solution for your scenario is indeed a viable option and can automate the process effectively. Here is a basic outline of how you can implement this:

1. Set up the Worksheet: Ensure that your termination date is entered in column D, the employee name is in column C, and the rehire date is in column E.
2. Write VBA Code: You'll need to add VBA code to your workbook to handle the hiding and unhiding of rows based on the termination and rehire dates.

Here is a sample VBA code to get you started:

Vba code is untested, please backup your file.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    Dim lastRow As Long
    
    'Get the last row in column D
    lastRow = Cells(Rows.Count, "D").End(xlUp).Row
    
    'Loop through each cell in column D
    For Each cell In Range("D2:D" & lastRow)
        'Check if the cell contains a termination date
        If IsDate(cell.Value) Then
            'Hide the row if a termination date is entered
            cell.EntireRow.Hidden = True
        Else
            'Unhide the row if no termination date is entered
            cell.EntireRow.Hidden = False
        End If
    Next cell
End Sub

This code will hide the entire row if a termination date is entered in column D, and it will unhide the row if the termination date is cleared. You will need to place this code in the code module for the worksheet where you want this functionality.

1. Handle Rehiring: To handle rehiring, you can clear the termination date and enter the rehire date in column E. This will keep the row unhidden.
2. Temporary Unhiding: To temporarily unhide a row without clearing the termination date, you can enter the employee name in cell C1. You can then modify the VBA code to check for this condition and unhide the corresponding row.
3. Testing and Adjustments: Test the code thoroughly to ensure it works as expected and make any necessary adjustments based on your specific requirements.

This should provide a basic framework for automating the hiding and unhiding of rows based on termination and rehire dates in Excel using VBA. You can further enhance the code based on your specific needs and workflow. The text, steps and code was created with the help of AI.

Simply implementing your requirement with formulas can be challenging due to the dynamic nature of showing and hiding rows based on changing data.

However, you can achieve some level of automation using helper columns and conditional formatting, although this may not fully reproduce the functionality of VBA.

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

[#3]

@NikolinoDE Thank you for the response .. I will put it to the test and see how it works out and let you know .. Thank you for your time and effort .. it is much appreciated.