' Set up your range, then:
Dim filteredRange As Range
With your Range
' Set AutoFilter to screen out non-compared objects
.AutoFilter Field:=1, Criteria1:=Array( _
3, _
9, _
18), _
Operator:=xlFilterValues
filteredRange = .Cells.SpecialCells(xlCellTypeVisible) ' returns the keepers
For i = filteredRange.count to 1 Step -1
If Cells(i, 1).EntireRow.Hidden = True Then Cells(i, 1).EntireRow.Delete
Next i
End With
Question: Could a Sort be used to get all the bad rows into one place and then just delete them all at once instead of looping through all the rows.
A sort is very fast. I know that when I delete a single row it takes about the same amount of time as deleting multiple rows.
Just a thought.
[B]Type=XlCellType and can be one of these XlCellType constants.[/B]
xlCellTypeAllFormatConditions. Cells of any format
xlCellTypeAllValidation. Cells having validation criteria
xlCellTypeBlanks. Empty cells
xlCellTypeComments. Cells containing notes
xlCellTypeConstants. Cells containing constants
xlCellTypeFormulas. Cells containing formulas
[URL="http://www.ozgrid.com/VBA/last-used-cell.htm"]xlCellTypeLastCell[/URL]. The last cell in the used range. Note this XlCellType will include empty cells that have had any of cells default format changed.
xlCellTypeSameFormatConditions. Cells having the same format
xlCellTypeSameValidation. Cells having the same validation criteria
xlCellTypeVisible. All visible cells
Sub DeleteRows()
Dim UnusedColumn As Long, LastRow As Long
LastRow = Cells.Find(What:="*", searchorder:=xlRows, _
searchdirection:=xlPrevious, LookIn:=xlValues).Row
UnusedColumn = Cells.Find(What:="*", searchorder:=xlByColumns, _
searchdirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
With Cells(1, UnusedColumn).Resize(LastRow)
.FormulaR1C1 = "=if(or(RC1=3,RC1=9,RC1=18),"""",""X"")"
.Value = .Value
On Error Resume Next
.SpecialCells(xlConstants).EntireRow.Delete
On Error GoTo 0
End With
MsgBox "Done!"
End Sub