Search, delete, move cell up?

[pjs15]

I copied from WordPress into an Excel workbook a list of 1,000 or so article titles with their corresponding authors, date published, etc. The result wasn't pretty, with blank and offset cells. I'm hoping to find a way to programmatically clean it up, though I am new to VBA and Excel macros.

Two main issues:

1) Every fourth line, this text shows up in Column A:

Edit*|*Quick*Edit*|*Trash*|*View

Is there a way to search for every instance of that string and delete the entire row in which the string resides?

2) In each row containing the title (Column A) and author (Column B), the entry for the date published (Column C) is just "Published" In the following row, A and B are blank, and the true date of publication, say 7/12/2017, is in Column C. Is there a way to search for every instance of "Published," delete the cell in which it resides, and perform the "Shift cell up" function?

I tried to attach a sample, but the Attach button didn't seem to be enabled.

Many thanks for any help.

pjs

[Glenn Kennedy]

If it's VBA you're after, I can't help. But....

Will you please attach a SMALL sample Excel workbook? Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[pjs15]

Thanks for the attachment tip, Glenn. Here it is (I think -- I uploaded a sample, but don't see it with this message).

pjs

[alansidman]

try this

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button