ExcelBanter - Copy rows of data (eliminating blank rows) from fixed layout

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Copy rows of data (eliminating blank rows) from fixed layout (https://www.excelbanter.com/excel-discussion-misc-queries/134708-copy-rows-data-eliminating-blank-rows-fixed-layout.html)

Copy rows of data (eliminating blank rows) from fixed layout

Worksheet 1 with fixed layout but random #s of rows for each category:

CategoryFruit:
Apple 1
<blank row
<blank row
Orange 2
<blank row

CategoryDrink:
Milk 3
<blank row
Soda 4
<blank row

Desired result on another worksheet with no blank rows:

Apple 1 CategoryFruit
Orange 2 CategoryFruit
Milk 3 CategoryDrink
Soda 4 CategoryDrink

Hope this makes sense. Please help!

Copy rows of data (eliminating blank rows) from fixed layout

Something to add:

Second worksheet will be populated automatically when the data in sheet 1 is
updated.

Thank you.

"Sweepea" wrote:

Worksheet 1 with fixed layout but random #s of rows for each category:

CategoryFruit:
Apple 1
<blank row
<blank row
Orange 2
<blank row

CategoryDrink:
Milk 3
<blank row
Soda 4
<blank row

Desired result on another worksheet with no blank rows:

Apple 1 CategoryFruit
Orange 2 CategoryFruit
Milk 3 CategoryDrink
Soda 4 CategoryDrink

Hope this makes sense. Please help!