To delete a row automatically ocne a closing date expires.

[Guest]

Hello

I have a spreadhseet where each row has a closing date entry in one column.

Is there any way to get the sheet to compare this date with today's date and
delete the row automatically once the closing date has passed?

 

[Guest]

A bit complicated if your close-off dates are all over the place so I have
assumed row 20. Try this in a standard module:-

Sub deletecolumns()
Dim myrange As Range
Set myrange = Range("20:20")
Cells(20, 1).Select
For Each c In myrange
c.Select
when = c.Value
where = ActiveCell.Column
If when < Now() Then Columns(where).Select
Selection.Delete
Next
Cells(20, 1).Select
End Sub

Mike

 

[Guest]

Hi again Mike

The closing dates are all in column K of the worksheet.

Generally we have up to 20 rows on each worksheet, each row being a single
vacancy.

 

[Guest]

Hi Mike

Thanks for that, but it's all a mystery to me.

Where do I type this and what is row 20?

Each row contains a vacancy whose closing date appears in column K

 

[Guest]

Hi Bollard,

Press ALT+ F11 and then double click the worksheet where your data is and
paste this code in

Private Sub Worksheet_Activate()
lastrow = Cells(Cells.Rows.Count, "K").End(xlUp).Row

For x = lastrow To 1 Step -1
Cells(x, 11).Select

If ActiveCell.Value <> "" And ActiveCell.Value < Now Then
where = ActiveCell.Row
Rows(where).Select
Selection.Delete
End If
Next
Cells(1, 11).Select
End Sub

The code will run whenever the sheet is selected and delte the entire row if
column K has a date earlier than today.

Mike

 

[Guest]

Hi Mike

I followed your instructions and copied and pasted your sub routine but
nothing happened. How do I close the window with the sub routine on it
without losing the contents?

 

[Guest]

Press Alt+ Q to close VB editor.

It will only execute when you select the worksheet you pasted the code in.
If the sheet is already seleceted then de-select it and then go back to it.

Mike

 

[Guest]

Hi Mike

Thanks for your patience.

Reading through the code, I'm a bit mystified, not that I have much
knowledge of programming.

Why does it read : Cells(x, 11) and later: Cells(1, 11)

Also, if the first row is Headings, will this affect the subroutine?

Keith

 

[Guest]

Bollard,

See comment in code below to describe what each bit does.

Hi Mike

Thanks for your patience.

Reading through the code, I'm a bit mystified, not that I have much
knowledge of programming.

Why does it read : Cells(x, 11) and later: Cells(1, 11)

Also, if the first row is Headings, will this affect the subroutine?

Keith

Finds the last used ro in column k and gets its
number(lastrow)
imagine the last used row is 100 therefore lastrow =100
A loop to make X every value between 100 and 1 counting
backwards.
selects the cell. The first cell would be row 100 column 11
which is column Kchecks the contents of the cell
A bit of dodgy programming on my part i could have
eliminated this line by changing the next line to rows(x).select
Ensure the cell selected at the end of the routine is K1. Once
again not
really necessary because it will be the selected cell anyway.

 

[Guest]

Hi Mike

Many thanks for explaining the programming.

I've still not managed to get it to work though.