VBA to extract rows that contain blank cells in specific columns

Afternoon all,

I'm undertaking some data validation and I'd like to extract rows where any mandatory fields haven't been populated. I've attached an example of the data I'm working with. The mandatory columns are highlighted in green and I'd like the rows that don't meet the criteria to be copied on to a separate tab (with the header at the top).

Thanks in advance,

Snook

Hi,

This is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

That said does the attached help. If npt make sure you do upload your production workbook, or at least a cut down representative copy.

Originally Posted by The_Snook

Hi Richard,

Thanks for this it looks just the job. I've been reading up on advanced filters but I'm still struggling to understand why your criteria range works? I'd be grateful if you could provide a brief explanation of how and why it works.


Snook

Hi,

The requirement is to extract all rows where there is a blank in ANY of the columns. This is an OR condition. i.e. each column is mutually exclusive and independent of whatever is in any other column.
To extract cells from every column the criteria range must have each criteria on its row.

If all the criteria are on the same row this is an AND condition and means get the rows where there is a blank in every column ON THE SAME DATA ROW

So the criteria uses the '=' sign which effectively means equals blank, and says
extract blank rows where Test Data C is blank,
OR where Test Data D is blank
OR where Test Data G is blank
...etc