Recognize certain text in a column and delete subsequent rows automatically

All,
This is a continuation of a previous post I made here (https://www.excelforum.com/excel-pro...ml#post4663914 that was solved. However, I am now trying to completely automate this spreadsheet based on how the raw data is gathered.

The easiest thing for me to do was to record a macro to delete the first twelve lines of data and then format the data from text to columns. I then COULD use the code that was mentioned in the link if it wasn't for one problem:

Rows 53,54,55 and then 55 rows from those rows (i.e. rows 108, 109, 110 and then rows 163, 164, and 165, and this repeats until the end of the data) have data that I don't want to be included because it basically is just text from when the data was copied from its original source as a text file. Is there a way for excel to recognize this text and then subsequently delete those rows. What each row says in column A is the following:

Row 53 Column A: Archive Report TA
Row 54 Column A: Time
Row 55 Column A: -----------------

Then as I stated before, this just repeats for rows 108, 109 and 110, and so on until the end of the data. Ideally, I'd like the code to be like the following:

Please Login or Register  to view this content.

Any help is appreciated. Thanks.

Hi Swishmak3r,

Excel has an Add-In for 2010 called Power Query, that can do this problem easily. You can install it from:
https://www.microsoft.com/en-us/down...4-2178abff26de

You would pull in your data and then remove all rows that you don't want.

https://www.youtube.com/watch?v=TWRiKhNnsU8

You can create a macro enabled workbook and save it as an .xlsm. A person could then copy data into it and perform the macro on any data that is there.

Was that the question?

The Add-In IS from Microsoft. It is built into Excel 2016 and is called Get & Transform. It is simply another tool in Excel.