Monday, 28 November 2011

Excel VBA - Delete all visible row except the header

Excel has some loveley properties for finding the last column and row and the ranges of worksheets that contain data. The one thing that always seems to be hard work is deleting a filtered range EXCEPT the header row. Here is my take on how to do it.

ws is a worksheet object. chDelete is a column number assigned for the records that need to be deleted. The column holds a value (in this case "DELETE") to identify the candidates for deletion. Depending on the volume and complexity of your data you may not need to sort the data before you filter it. If you need to preserve the original data order you will need to ensure that you have created a column with the existing row numbers in it first OR have another way of restoring the original order.


ws.UsedRange.Select
Selection.Sort Key1:=Range(Cells(2, chDelete).Address), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal

Selection.AutoFilter
Selection.AutoFilter Field:=chDelete, Criteria1:="DELETE"
ws.Range(Cells(2, 1).Address, ws.UsedRange.SpecialCells _
(xlCellTypeLastCell).Address).SpecialCells(xlCellTypeVisible).EntireRow.Delete