Need to delete a certain table row based on a specific cell value (Date)

[pipoliveira]

Dear All,

I hope you are all well.

I have a table to update employees Out Of Office. That table have 3 columns, the first for the employee name, the second is to maintain the date "from" and the 3rd to maintain the date as "To".

I wrote a code to auto delete the rows content where the date in the 3rd column is past.

The code I have is the following:

Private Sub Worksheet_Activate()
With Range("I42:N" & Cells(Rows.Count, 4).End(xlUp).Row)
    .AutoFilter Field:=3, Criteria1:="<" & CLng(Date), Operator:=xlAnd
    .Offset(1).ClearContents
    .AutoFilter
End With
End Sub

Now the problem I have is within the table.

Each column have 2 merged cells and I think that's why I am having an error with the code as it mentions that cannot delete merged files.

I cannot use single cells as the sheet I have the code contains several tables on top and beside this one.

Any ideas on how to postpone this error?

Your assistance on this matter will be highly appreciated.

Best regards,
Filipe

 

[Smallman]

Hi Pipoliveria

Any ideas on how to postpone this error?

Yep take out the Merged Cells and never again in your life use them. Great example as to why merged cells are bad news. Once you start coding with merged cells almost all is lost. Of course I am exaggerating for effect however it is very very good practice not to merge cells. Use Centre Across Selection.

Format Cells - Alignment - Horizontal Dropdown - Centre Across Selection

Same look and feel without the loss of fidelity. That will fix your problem.

Take care

Smallman

 

[pipoliveira]

Hi Pipoliveria



Yep take out the Merged Cells and never again in your life use them. Great example as to why merged cells are bad news. Once you start coding with merged cells almost all is lost. Of course I am exaggerating for effect however it is very very good practice not to merge cells. Use Centre Across Selection.

Format Cells - Alignment - Horizontal Dropdown - Centre Across Selection

Same look and feel without the loss of fidelity. That will fix your problem.

Take care

Smallman

Thanks Smallman,

I know now, but as I mentioned, I have a sheet only with tables that are used by key users.

Whenever data they changed in that sheet, it will reflect in all other sheets (About 24 of them).

I think now is too late for it, as the tool is Live and is constantly been updated. I will have to tell them to delete the entries that are overdue/in the past manually.

Thanks a million for your quick and helpful reply.

Best regards,
Filipe