Need help removing "bad" data from Excel File that is exported from a PDF daily

Hi All,

I receive an excel file daily that is exported from a PDF (unfortunately there isn't another way) and I am hoping that someone can assist me with cleaning this up using VBA. The file has headers on each page that are not needed, my issue is that the exporting process never exports the file the same way (sometimes there are 8 rows on the header and sometimes there are 9 or 10. I do know that if there is a date "dd/mm" in column H that the line can stay and that if column K is blank it can be removed. I am hoping that someone can help me use those variables to automatically clean the sheet so that I don't spend 20-30 minutes every day removing all of the unneeded data.

What I know:

If column H has a date and column K has data- keep
If column H has a date and column K is blank- remove row

That should remove most of my struggles, I just don't know how to write the script to complete it. I have added the file- please let me know if you need any additional details!

I added the file that I manually cleaned up- KS OJR- 03202024


Thank you in advance for any help on this,

Adam

Please Login or Register  to view this content.

UPDATED code and file

If you did not realise, I copied the raw data from "KS OJR 03202024" into sheet "Table 1" of "KS OJR 03202024 TEST" rather have the data in separate file.

Click "RUN" button on sheet "KS OSJ" (wrong name!)

Please Login or Register  to view this content.

Yes, but CLEAR ("ClearContents") "Table 1" to avoid leaving extraneous data as the new data might be a smaller range than the new replacement.

Then just "RUN".

I updated the macro (post #8) to ensure it activated the correct sheet.

Please Login or Register  to view this content.

Updated file

You're very welcome and thank for the rep.

Come back to the forum if you ever need help in the future: there is always someone willing and able to assist you.

Apologies: I thought I had posted file with the above requirement.