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.
The code to achieve the above is as follows.
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.
Sub ColourikeData() 'Excel VBA to highlight column differences.
Range("A11:A20").ColumnDifferences([a11]).Interior.Color=vbYellow
End Sub
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.
Sub MoveLikeData()'Excel VBA to move column differences.
Range("A11:A20").ColumnDifferences([a11]).EntireRow.Copy Sheet2.[A2]
End Sub
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.
The blue cell contains the point of difference and yellow represents the cells which VBA will move.
Sub MoveLikeData() 'Put the point of difference above the data.
In the above example the data rests in Column B and the criteria in BLUE is in cell B9.
Range("B9:B20").ColumnDifferences([b9]).EntireRow.Copy Sheet1.[a1]
End Sub
In the above example the data rests in Column B and the criteria in BLUE is in cell B9.
