Results 1 to 5 of 5

Thread: Need to Delete Multiple Row based on Conditions

  1. #1
    Junior Member
    Join Date
    Mar 2013
    Posts
    2
    Rep Power
    0

    Need to Delete Multiple Row based on Conditions

    Hi, I am a noob to VBA. I would like to create a macro that will delete entire rows based on multiple values. I have a spread sheet with several columns. I want the Macro to look in Column AA and if the Value = "Target", then look in column C for the value "3", then delete the entire row. Any help would be greatly appreciated.

    Joshua
    Last edited by jdlee; 03-22-2013 at 04:29 AM.

  2. #2
    Junior Member
    Join Date
    Mar 2013
    Posts
    2
    Rep Power
    0

    forgot something

    I would like to mention the name of the sheet I am using is called "CurrentF"

  3. #3
    Junior Member
    Join Date
    Mar 2013
    Posts
    20
    Rep Power
    0
    Try This Easy and works every time

    Code:
    Sub test()
    Range("A1").Select
    Do While ActiveCell.Address(0, 0) <> Range("A65536").End(xlUp).Offset(1, 0).Address(0, 0)
    If LCase(ActiveCell.Value) = "target" And ActiveCell.Offset(0, 2).Value = 3 Then
    ActiveCell.EntireRow.Delete
    ActiveCell.Offset(-1, 0).Select
    End If
    ActiveCell.Offset(1, 0).Select
    Loop
    
    
    End Sub

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Code:
    Sub DeleteRows()
    
        Dim lng As Long
        With Worksheets("CurrentF")
            lng = .Cells(Rows.Count, "AA").End(xlUp).Row
            For lng = lng To 1 Step -1
                If Trim(.Cells(lng, "AA").Value) & Trim(.Cells(lng, "C").Value) = "Target3" Then
                    .Rows(lng).Delete
                End If
            Next lng
        End With
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Another way (uses no loops)...
    Code:
    Sub DeleteThreesAndTargets()
      Dim LastRow As Long, UnusedColumn As Long
      LastRow = Cells(Rows.Count, "C").End(xlUp).Row
      UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                     SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column
      With Range(Cells(1, UnusedColumn), Cells(LastRow, UnusedColumn))
        .Value = Evaluate("IF((C1:C" & LastRow & "=3)*(AA1:AA" & _
                 LastRow & "=""target""),C1:C" & LastRow & ","""")")
        On Error Resume Next
        .SpecialCells(xlConstants).EntireRow.Delete
        On Error GoTo 0
      End With
    End Sub
    This method does have a restriction of no more than 8192 individual ranges (areas) of rows (contiguous rows count as one area) meeting your conditions for deletion. That means you can have at least 16394 rows of data before the code might give you trouble.

Similar Threads

  1. Delete Rows Based on Conditions
    By AbiG2009 in forum Excel Help
    Replies: 6
    Last Post: 12-26-2018, 01:24 PM
  2. Replies: 3
    Last Post: 05-23-2013, 11:17 PM
  3. Nested If Formula With Multiple Conditions
    By lprc in forum Excel Help
    Replies: 10
    Last Post: 04-22-2013, 07:27 PM
  4. Replies: 2
    Last Post: 03-05-2013, 07:34 AM
  5. Replies: 1
    Last Post: 12-04-2012, 08:56 AM

Tags for this Thread

Posting Permissions

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