• Macro to delete rows with 1 in column B

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Macro to delete rows with 1 in column B

    Author
    Topic
    #482745

    I need a macro to delete the entire row where there is a 1 in column B. Also, to delete one row above. So where there is a 1 in column B, I need that row deleted and the one above. Any help is greatly appreaciated.

    Viewing 0 reply threads
    Author
    Replies
    • #1329244

      Linda,

      This should do the trick:

      Code:
      Option Explicit
      
      Sub MyDelete()
      
         Dim lLastRow As Long
         Dim lCurRow As Long
         Dim zRowRng As String
         
         Application.ScreenUpdating = False
         
         lLastRow = Cells(Rows.Count, "B").End(xlUp).Row
         Cells(lLastRow, 2).Select
           
         Do While ActiveCell.Row() >= 2
         
           If ActiveCell.Value = 1 Then
             lCurRow = ActiveCell.Row()
             zRowRng = Format(lCurRow - 1) & ":" & Format(lCurRow)
             Rows(zRowRng).EntireRow.Delete
           End If
         
           ActiveCell.Offset(-1, 0).Select
          
         Loop
      
         If ActiveCell.Value = 1 Then ActiveCell.EntireRow.Delete
         
      End Sub
      

      :cheers:
      30629-MyDeleteStartData30630-MyDeleteResults

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    Viewing 0 reply threads
    Reply To: Macro to delete rows with 1 in column B

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: