How could I set set up a table such that, if I copied the whole table to a
new sheet only the rows containing data would be copied, and the remaining
rows would be blank.

Example:

If rows 1 and rows 3 contained cells with data and rows 2 and 4 were empty,
how could I set up a filter (or formula/VB macro etc) such that any data
manually entered into a row would display but if the filter for that row were
set to a "N", for example, the row would not display data?

Many thanks