I'm looking for a formula (or macro if it would be easier) that will lookup values in a multiple vertically stacked tables which can have some empty rows at the bottom of each, and create another list on a separate sheet without any empty rows between the data. An example of this is (R= row#s):

Sheet1
R1Table1-Header
R2"text a"
R3"text b"
R4
R5
R6Table1-End
R7
R8Table2-Header
R9"text c"
R10"text d"
R11"text e"
R12
R13Table2-End
R14
R15Table3-Header
R16"text f"
R17"text g"
R18
R19
R20Table3-End

-Where there are multiple tables in Sheet1 of the same length, organized vertically with one empty row between each, and the contents are dynamically filled via a separate userform. I want to create a single table or range (unsure if it is easier to make it a table or just a formatted range) on Sheet2 that contains all the contents of the tables from Sheet1 as a continuous list without any blank rows between the data. An example is:

Sheet2
R1Table/List Header
R2"text a"
R3"text b"
R4"text c"
R5"text d"
R6"text e"
R7"text f"
R8"text g"


Any suggestions?

-Felipe