Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Delete rows when column contains a specific character

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •