Remove rows above a cell with certain contents?

[Charley]

I have a government-created workbook with multiple "tables" stacked in a worksheet. The only thing that designates any block of numbers and text as a table is that each one starts with an empty row, has a table title in column A, another empty row, and then column titles followed by typical table contents.

Is there a way to search for a table's title in a worksheet to discover the number of rows that must be deleted to move the table to row 1? And is there an equivalent way to find the first empty row below the table so that I can delete any content below it?

Thanks!

 

[Bill Szysz]

Hi Charley,
Please, provide some example file.
For now, i think you should be interested Table.RemoveFirstN and Table.FirstN functions.

 

[BernardBrussels]

Hi Charley

I tryed to create a solution...

You have 2 files:
* Tables.xlsx : data file that contains 3 tables
* Separator.xlsx :
- contains a table with the path to the data file
- Raw : all the data unfiltered
- headers : just the lines of the headers (attention the ifrst column of the header need to be hard coded in the query
- All Tables : lines from the RAW but with the table number added
- then a sheet to show each table.

Regards,

Bernard

PS: we could use the empty row as header lines...