Delete range of cells based on a cell value (Date).

[pipoliveira]

Dear all,

I hope you are all well.

I am looking for a VBA code that will delete a range of cells based on one of the cells value.

I have attached a file into this thread.

This is an Out-Of-Office table that is maintained by one person and that table has the following headers:

Employee - From - To

What I need is a VBA code that deletes the line that the date in column "To" is past (from today).

Is that a way to do it? Maybe on the event "Worksheet_Calculate"?

Your assistance on this matter will be highly appreciated.

Best regards,
Filipe Oliveira.

[nilem]

Hi Filipe,
maybe so

Please Login or Register  to view this content.

[pipoliveira]

Hi Nilem,

Thank you for your example.

Sorry for replying only now but I did replied to you before, maybe my reply did not went through for some reason.

Your code did deleted the entry but it deletes the table row. What I need is to delete the values only, not the entire table row.

If you test your code on the example I have attached, you can see that every time you change sheets, the code deletes a row from the Table, even with data or not.

Any other recommendations on this matter will be highly appreciated.

Best regards,
Filipe

[stnkynts]

try using .ClearContents instead of .Delete

[pipoliveira]

Thanks a million stnkynts,

The answer I was looking for. I should have known better.

Really appreciated by your help.

Best regards,
Filipe Oliveira.

[shyamhappy]

Yes I feel that stnkynts is correct, instead of using .Delete use .ClearContents.

_________________________________________________________________________________________________________________________________________
Please click * if something has helped

[pipoliveira]

Sorry guys, I have found an error on the code provided by nilem.

The code works perfectly when there's dates on the past, but if there is a date that is still current or in the future, I receive the following error:
"Run-time error '1004
No cells found"

I use this code under "Workbook_Activate".

The other thing that happened was the code deleted everything under row D.

Any suggestions will be highly appreciated.

Best regards and thanks for your help in advance.

Best regards,
Filipe

[nilem]

Hi Filipe,
I was not able to reproduce the error. Could you show an example of your data file where the error occurs.

[pipoliveira]

Hi nilem,

Once again, my reply, for some reason, did not came through. Sometimes this forum is not available or crashes.

The problem with the code is that deletes all contents of the row.

I have few series of tables within the same sheet. Running this code, it will not only delete the row from the required table, but it deletes all contents of the row (e.g. row 3).

I have updated the file with other table beside it. Try to add a date in the past, change sheets and you will see that the contents of the other table (from same row) will be deleted as well.

Resuming, I just want the contents inside the table "Out Of Office" to be deleted.

Many thanks in advance and kind regards,
Filipe

[nilem]

Hi Filipe,
try

Please Login or Register  to view this content.

[pipoliveira]

Hi nilem,

Thanks a million for your reply, it did worked on the example file I sent you.

On my current file, this macro does not work and I believe for the fact that the table I have contains 3 columns as per the example, but each column have merged cells.

I will try to sort somehow and find a code that will work as well with merged cells.

Thanks a million for your assistance.

Best regards,
Filipe