Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: VBA Code: Delete Entire Row based on Condition

  1. #1
    New Member
    Join Date
    Jun 2010
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Code: Delete Entire Row based on Condition

    Still learning VBA - I am trying to delete an entire row based on a condition in one cell in the row. Typically I would just filter on that value and delete the rows, but I am not sure if that is a possibility in VBA code. Can you provide the code if not too complex.

    Select Cell A1 if value is 100 delete entire row, else skip to next row. Then loop through each row in the spreadsheet till all rows with selected cell equal to 100 are deleted.

  2. #2
    Board Regular
    Join Date
    Sep 2007
    Location
    New Jersey USA
    Posts
    3,457
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code: Delete Entire Row based on Condition

    Quote Originally Posted by jbeck View Post
    Still learning VBA - I am trying to delete an entire row based on a condition in one cell in the row. Typically I would just filter on that value and delete the rows, but I am not sure if that is a possibility in VBA code. Can you provide the code if not too complex.

    Select Cell A1 if value is 100 delete entire row, else skip to next row. Then loop through each row in the spreadsheet till all rows with selected cell equal to 100 are deleted.
    Maybe:

    Code:
    Sub jbeck()
    Dim i As Long
    Dim LR As Long
    
    Application.ScreenUpdating = False
    
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = LR To 2 Step -1
    
        If Range("A" & i) = 100 Then
            Range("A" & i).EntireRow.Delete Shift:=xlUp
        End If
        
    Next i
    
    Application.ScreenUpdating = True
       
    End Sub
    Last edited by John Davis; Jun 25th, 2010 at 02:24 PM. Reason: change code

  3. #3
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code: Delete Entire Row based on Condition

    Here's the VBA for an autofilter
    Code:
    Sub FilterDelete()
    Dim rspn As Variant
    LR = Cells(Rows.Count, "A").End(xlUp).Row
    rspn = InputBox("What do you want to delete?")
    Range("$A2:$A" & LR).AutoFilter Field:=1, Criteria1:=rspn
    Range("$A$2:$A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End Sub
    Mucjh more efficient than a loop!!

    lenze
    If you have to tell your boss you're good with Excel, you're NOT!!
    All I know about Excel I owe to my ignorance!
    Scotch: Because you don't solve great Excel problems over white wine

  4. #4
    New Member
    Join Date
    Jun 2010
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code: Delete Entire Row based on Condition

    If I have 2 criteria on 2 separate columns -A & B, how do I modify the macro to work?

    As long as 1 of the criteria is met, the row should be deleted.


    Thanks

  5. #5
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA Code: Delete Entire Row based on Condition

    Quote Originally Posted by miyaboy View Post
    If I have 2 criteria on 2 separate columns -A & B, how do I modify the macro to work?

    As long as 1 of the criteria is met, the row should be deleted.


    Thanks

    Try something like this

    Code:
    Sub jbeck()
    Dim i As Long
    Dim LR As Long
    Application.ScreenUpdating = False
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    For i = LR To 2 Step -1
        If Range("A" & i) = 100 Or Range("B" & i) = "Completed" Then
            Rows(i).Delete
        End If
    Next i
    Application.ScreenUpdating = True
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  6. #6
    New Member
    Join Date
    Jun 2010
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code: Delete Entire Row based on Condition

    Sorry but the code does not work. Nothing happens after I run the macro.

  7. #7
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA Code: Delete Entire Row based on Condition

    Post the actual code that you have tried.
    HTH, Peter
    Please test any code on a copy of your workbook.

  8. #8
    New Member
    Join Date
    Jun 2010
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code: Delete Entire Row based on Condition

    Sub jbeck()
    Dim i As Long
    Dim LR As Long
    Application.ScreenUpdating = False
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    For i = LR To 2 Step -1
    If Range("A" & i) = "WOSE" Or Range("B" & i) = "WOSE" Then
    Rows(i).Delete
    End If
    Next i
    Application.ScreenUpdating = True
    End Sub

  9. #9
    New Member
    Join Date
    Jun 2010
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code: Delete Entire Row based on Condition

    Okay for the first two columns A & B as long as "WOSE" appears in a row, I want the row to be deleted.

    The "WOSE" is being generated from the excel formula IF(c2="ddd","WoSE","NONE")

  10. #10
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA Code: Delete Entire Row based on Condition

    Then you need

    If ucase(Range("A" & i).value) = "WOSE" Or ucase(Range("B" & i).value) = "WOSE" Then
    HTH, Peter
    Please test any code on a copy of your workbook.

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
  •