Filtering blank rows from a column

I note you've already investigated filtering.

You could look into setting up a data query on the 2nd worksheet that references the raw data on the first one, and by using filtering in the query (using the WHERE condition) the 2nd sheet should then automatically show the filtered data.

See menus Data, Import External Data, New Database Query.

You will have to point the data query to the range in the 1st sheet, and this should have field names in the columns and be covered by a named range. The data and file must be saved before setting up the data query.

As you will be pasting in new raw data from time to time (presumably with different number of rows) you should define the named range to extend far beyond the likely number of rows (the data query will filter out anything that's blank anyway).

After pasting in a new set of raw data, save the file, then go to the 2nd sheet and right-click on the filtered data and select Refresh Data from the pop up menu.


This should work ok, but I have found that data queries fail when workbooks are moved, emailed, or renamed (when a bit of VBA code may be needed to ensure the data query always finds the correct data source).

Thank you Loz, that's excellent information.

Thanks again!