Delete rows when column contains a specific character

[MikeD]

I know how to delete rows if cell in specified column is blank. This time I would like to delete whole row if a cell in a chosen column contains say C for complete or O for outstanding
This message was edited by MikeD on 2002-03-09 18:22

 

[Dave Hawley]

Hi Mike


I have many examples of deleting rows based on a criteria here:
http://www.ozgrid.com/VBA/VBACode.htm

Or if you opt for slower method of a loop, you can speed things up a bit by using:

Sub DoIt()
Dim rRange1 As Range, rRange2 As Range
Dim rTheRange As Range
Dim rCell As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On Error Resume Next
Set rRange1 = Selection.SpecialCells(xlCellTypeConstants, xlTextValues)
Set rRange2 = Selection.SpecialCells(xlCellTypeFormulas, xlTextValues)

If rRange1 Is Nothing Then
Set rTheRange = rRange2
ElseIf rRange2 Is Nothing Then
Set rTheRange = rRange1
Else
Set rTheRange = Range(rRange1, rRange2)
End If

On Error GoTo 0
For Each rCell In rTheRange
If rCell = "C" Or rCell = "O" Then
rCell.EntireRow.Delete
End If
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training
If it's Excel, then it's us!
This message was edited by Dave Hawley on 2002-03-09 18:43

 

[MikeD]

I know how to delete rows if cell in specified column is blank. This time I would like to delete whole row if a cell in a chosed column contains say C for complete or O for outstanding


Many thanks for your help.