Consulting

Results 1 to 4 of 4

Thread: Solved: Delete Rows with Condition

  1. #1
    VBAX Mentor fredlo2010's Avatar
    Joined
    Dec 2011
    Location
    Florida, USA
    Posts
    374
    Location

    Question Solved: Delete Rows with Condition

    Hello guys,

    Here I am again.

    I am trying to delete rows in my worksheet that meet a criteria. I want to delete all rows that are empty in column "C" and do not start with "I want" or "Keep" in column "A"
    (this is all dummy text)

    I have attached an sample of my workbook with two sheets one with the data and the desired output.

    Sample.xlsx

    Thanks a lot guys
    Feedback is the best way for me to learn


    Follow the Armies

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,232
    Location
    [VBA]Public Sub ProcessData()
    Dim LastRow As Long
    Dim i As Long
    Dim cell As Range

    Application.ScreenUpdating = False

    With ActiveSheet

    LastRow = .UsedRange.Rows.Count
    For i = LastRow To 2 Step -1

    If .Cells(i, "C").Value2 = "" And _
    Not .Cells(i, "A").Value Like "*I want*" And _
    Not .Cells(i, "A").Value Like "*Keep*" Then

    .Rows(i).Delete
    End If
    Next i
    End With

    Application.ScreenUpdating = True
    End Sub
    [/VBA]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    750
    Location
    Here's one more.
    [VBA]Public Sub DelRows()
    Dim rng As Range, rngDel As Range
    On Error Resume Next
    For Each rng In Range("C2:C" & UsedRange.Rows.Count).SpecialCells(4).Offset(, -2)
    If Not (rng.Value Like "I want*" Or rng.Value Like "Keep*") Then
    If rngDel Is Nothing Then
    Set rngDel = rng
    Else
    Set rngDel = Union(rngDel, rng)
    End If
    End If
    Next rng
    On Error GoTo 0
    If Not rngDel Is Nothing Then rngDel.EntireRow.Delete
    End Sub[/VBA]
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

  4. #4
    VBAX Mentor fredlo2010's Avatar
    Joined
    Dec 2011
    Location
    Florida, USA
    Posts
    374
    Location
    Thanks guys,

    Both codes worked. I only have one question to xld.

    Is there a reason why you used Value2 and not Value?

    Thanks a lot guys
    Feedback is the best way for me to learn


    Follow the Armies

Posting Permissions

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