Filtering blank rows from a column

I've got a "raw data" sheet that I would like to condense into something a little easier to read on a different sheet. Let me try to explain:

Columns A,B,C, & D may be up to 200 rows long but some cells are empty. Row 1 has data but then 2 & 3 are empty. 4 has data but 5,6,& 7 are empty...

Column D has a qualifier (such as "active" or "inactive") that tells if the entire row is needed.

Is there anyway to use VLOOKUP on the word active to copy the rows to a new sheet BUT exclude the blank rows? My goal is to condense the columns excluding any blank rows. I know about auto-filtering but I want this to be an automatic deal so that I can just paste my data in sheet one and then goto sheet 2 and its all there nice and pretty...

Thanks, any help would be greatly appreciated.
Jeff