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?

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

"bollard" wrote:

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?

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.

"Mike" wrote:

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

"bollard" wrote:

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?

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

"Mike" wrote:

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

"bollard" wrote:

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?

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

"bollard" wrote:

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

"Mike" wrote:

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

"bollard" wrote:

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?

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?

"Mike" wrote:

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

"bollard" wrote:

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

"Mike" wrote:

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

"bollard" wrote:

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?

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

"bollard" wrote:

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?

"Mike" wrote:

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

"bollard" wrote:

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

"Mike" wrote:

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

"bollard" wrote:

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?