Create continuous non blank table based on merge of separate dynamically filled tables

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

Sure, sorry I realize my question is rather vague! I have created a sample workbook where Sheet1 contains the tables which are populated through a separate sheet/user forms, and Sheet2 has the table (could simply be a range) with the data populated as I need it to appear.

Whenever the tables in Sheet1 get row data added/deleted, I want the master table in Sheet2 to be updated to contain all the data grouped without any spaces between the "City" table data.

I have been trying some variations of array formulas that find the last empty cell/row in an array but that does not seem to address my issue so I wonder if the solution might be with VBA.

Update: I have found an array formula solution that is getting close to solving my problem! The formula is (entered using Ctrl+Shift+Enter):

=IF(ROWS($2:2)<=COUNTA($B$2:$B$87),INDEX($B$2:$B$87,SMALL(IF($B$2:$B$87 <>"",ROW($B$2:$B$87)-MIN(ROW($B$2:$B$87))+1),ROWS($2:2))),"")

Which returns the contents of the "City" columns in the tables on Sheet1 of my attached sample workbook, in order by skipping blank rows to the next row with a non-empty cell.
This can be copied across for all the columns, however the issue is that it will also return the table header names!! I have tried inserting an OR condition in the IF statement such as: --IF(OR($B$2:$B$87 <>"",$B$2:$B$87 <>"City"),-- but this does not seem to work (neither does the AND condition).

Any help would be greatly appreciated!

stanleydgromjr,

Thanks for your code it works great!. I modified it somewhat since I do not want to create a new sheet and the "Calc" columns will have formulas so they should not be cleared each time, however the underlying loop for resorting the data is exactly what I needed.

Regards-
Felipe