Highlight Column Differences with Excel VBA
One of the little known features of the special cells family is the ColumnDifferences command. Its implications in VBA are far reaching as you can isolate data based on a condition without the Autofilter or a looping construct. For example, if you wanted to highlight all of the cells in Column A which did not meet a specific criteria ColumnDifferences could be used in the following way.
You notice that in [a11] the cells value is England. After the procedure is run the cells which do not equal England are highlighted. This is broader implications if you want to move data based on one criteria.
The following will copy all of the data highlighted into Sheet2. With one criteria this is a very efficient way to move data from one sheet to another.
I have since revisited the idea of column differences with VBA. It occurred to me it would make a lot of sense to put the cell you want the data to be different to above your header row. That way the header and all of the data beneath the header will be moved into the first row of the new sheet. It is an exceptional way to move data. A really neat VBA trick.
In the above example the data rests in Column B and the criteria in BLUE is in cell B9.