Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel
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 its a date driven so I need it so when there dates spire they drop off the database. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel
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 On Dec 20, 4:26 pm, Kracken wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|