In the article highlight column differences I touched on the VBA procedure:
The article was introductory where I did a bit of colour coding of cells which did not match and copied the non matching cells to a fresh sheet. Well this technique can be used in a multitude of ways. We can remove all of the rows in a sheet which do not meet criteria as specified in a particular cell.
For example if we had a listing of sales people we might want to isolate only a single sales person order quantity. To do this with the column differences code is quite efficient. The only thing to remember with this method is you need to have the criteria in the same column as the range. This is best included in the cell above the Header. That way it is part of the range.
Option Explicit
Sub RemQuickly()
Dim rng as Range
Set Rng = [F9:F1000].ColumnDifferences([F9])
rng.EntireRow.Delete
End Sub
Notice the blue cell above the Sales Person Header. After the blue button is pressed, everything which does not match that sales persons name will be deleted. The red button is added to bring the model back to what it was at the start - to restore.
For this procedure to restore your data, we use the following procedure where the raw data is stored inside a sheet in the workbook.
Sub RestoreData()
Dim sh as Worksheet
Set sh = Sheet4
sh.[a1].CurrentRegion.Copy [a10]
End Sub
An Excel file has been included to help demonstrate this method between this method and the copy/highlight method I put on the main site years ago you should have everything you need.
