To delete a row automatically ocne a closing date expires.
 

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?

To delete a row automatically ocne a closing date expires.
 

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?

To delete a row automatically ocne a closing date expires.
 

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?

To delete a row automatically ocne a closing date expires.
 

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?

To delete a row automatically ocne a closing date expires.
 

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?

To delete a row automatically ocne a closing date expires.
 

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?

To delete a row automatically ocne a closing date expires.
 

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?

To delete a row automatically ocne a closing date expires.
 

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

"Mike" wrote:

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?

To delete a row automatically ocne a closing date expires.
 

Bollard,

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

"bollard" wrote:

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

"Mike" wrote:

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
Finds the last used ro in column k and gets its
number(lastrow)
imagine the last used row is 100 therefore lastrow =100


For x = lastrow To 1 Step -1
A loop to make X every value between 100 and 1 counting
backwards.

Cells(x, 11).Select
selects the cell. The first cell would be row 100 column 11
which is column K

If ActiveCell.Value < "" And ActiveCell.Value < Now Then
checks the contents of the cell

where = ActiveCell.Row
A bit of dodgy programming on my part i could have
eliminated this line by changing the next line to rows(x).select

Rows(where).Select
Selection.Delete
Delete the row
End If
Next
decrease x by 1
Cells(1, 11).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.
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?

To delete a row automatically ocne a closing date expires.
 

Hi Mike

Many thanks for explaining the programming.

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

"Mike" wrote:

Bollard,

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

"bollard" wrote:

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

"Mike" wrote:

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
Finds the last used ro in column k and gets its
number(lastrow)
imagine the last used row is 100 therefore lastrow =100


For x = lastrow To 1 Step -1
A loop to make X every value between 100 and 1 counting
backwards.

Cells(x, 11).Select
selects the cell. The first cell would be row 100 column 11
which is column K

If ActiveCell.Value < "" And ActiveCell.Value < Now Then
checks the contents of the cell

where = ActiveCell.Row
A bit of dodgy programming on my part i could have
eliminated this line by changing the next line to rows(x).select

Rows(where).Select
Selection.Delete
Delete the row
End If
Next
decrease x by 1
Cells(1, 11).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.
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?