Excel

[Kracken]

Need to make sheet with dates that self drop off and or expire, making this
to keep track of soldiers incentives in the military, and would really love
any help in making this happen. The way it would work is on month to month
bases soldiers get paid, some drop off system some come on but it’s a date
driven so I need it so when there dates spire they drop off the database.

 

[ilia]

Depending on how you have your sheet set up, and what data it tracks,
but suppose you have column D contains the expiration dates. The
worksheet is called "Soldier List". In your workbook's code module,
you can add the code listed below - it will remove any expired rows
whenever the workbook is opened. You can use code other than
Rows(i).Delete to copy the data to an archive worksheet, or
something. Adjust the two constants at the top to suit. Post back
with questions.


Private Sub Workbook_Open()
Dim i As Long
Dim maxRow As Long
Const expirationDateColumn As Integer = 4
Const worksheetName As String = "Soldier List"

Application.ScreenUpdating = False

With ThisWorkbook.Worksheets(worksheetName)
maxRow = .UsedRange.Rows.Count
i = 2
Do While (i <= maxRow)
If (CLng(.Cells(i, _
expirationDateColumn).Value) _
< CLng(Now())) Then
.Rows(i).Delete
maxRow = maxRow - 1
Else
i = i + 1
End If
Loop
End With

Application.ScreenUpdating = True
End Sub