Delete entire row in table if cell in column P contains any value (numbers or text)

Hi I have a problem and cannot seem to find a suitable solution anywhere.

I have a table (dynamic named range "datalog") Information is pasted from another source - as text.

I would like to delete the "entire row" if a value (text or numbers) is displayed in column P. Essentially only leaving the rows with no data left in column P.

What is the most efficient way of doing this in VBA. (as I have to automate this procedure)

Data sets could be quite large.

Thanks Robert.

Unfortunately This deletes the blanks leaving the cells with value only.... Am I doing something wrong?

I have attached a copy of the file

Robert,

Thanks for taking the time and effort to solve this problem. It woks perfectly.

Much appreciated.

Hi Robert,

When I apply your macro to the real data - where the data is formatted as a "TABLE"

It does not delete all rows where there is data in column 'P'

If I convert the TABLE back to a range - The macro works perfectly.

Any thoughts?

As far as I know "Table" is a somewhat protected area where one can delete singel rows but trying to do so for a number of rows is a "no no" as you found out.

If the layout of your working file is like the uploaded file one can also use autofilter to find rows in P column with value. Something like this perhaps that convert a table to a range filter and delete and sets range back to a table.

Please Login or Register  to view this content.

Alf