VBA coding for Row Deletion

[aggierichie]

This should be a fairly easy one but I cannot seem to nail it down.

I have a simple 7 column sheet. This sheet feeds into other workbooks but is itself only a 1 sheet workbook.

What i need to happen is for the rows to auto delete when the dates expire. So, if an employee is returning to work on 3/15 (and placed his return date in Column D) then excel will automatically DELETE the corresponding Row on 3/15.

In Short. If the date in column D is todays date or later, Delete the corresponding row.

IF someone could help me out with the code on this I would be forever in your debt. I have tried several codes i have found on here but have not been able to get it to work correctly.

 

[John Davis]

This should be a fairly easy one but I cannot seem to nail it down.

I have a simple 7 column sheet. This sheet feeds into other workbooks but is itself only a 1 sheet workbook.

What i need to happen is for the rows to auto delete when the dates expire. So, if an employee is returning to work on 3/15 (and placed his return date in Column D) then excel will automatically DELETE the corresponding Row on 3/15.

In Short. If the date in column D is todays date or later, Delete the corresponding row.

IF someone could help me out with the code on this I would be forever in your debt. I have tried several codes i have found on here but have not been able to get it to work correctly.

Do you mean something like this?

Sub aggierichie()
'First in a separate cell I'd put for example A1 =Today()
Dim cl As Range
Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

For Each cl In Range("D2:D" & lr)

    If cl.Value >= Range("A1") Then
    
        cl.EntireRow.Delete
        
    End If
    
Next
    


End Sub

 

[stnkynts]

If you wanted it to automatically happen you could put in in the worksheet_selection change like this:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lastrow As Long, icell As Long
lastrow = Range("D" & Rows.Count).End(xlUp).Row
For icell = 1 To lastrow
        If Range("D" & icell).Value <= Date Then
           Range("A" & icell).EntireRow.Delete Shift:=xlUp
        End If
Next icell
End Sub

Dont need to put =Today() in A1 for this.

 

[aggierichie]

John Davis, Thanks for the reply but I wanted it to automatically happen and I cant seem to get this one to automatically go

stnkynts- I would like it to happen automatically but i am not 100 pct sure where to put in that formula. I am not super proficient with VBA but am teaching myself as i go. Sorry to be a pain but could you let me know where to put that and ill drop it in.

 

[stnkynts]

If you are using excel 2007 gain access to the developer tab and click Visual Basic. Or just hit Alt +F11.

Right click on whichever sheet has your "7 columns" in it. Click View Code. Paste the code i provided there.

 

[aggierichie]

stnkynts- thank you so much. this solved my problem!!!