Announcement

Collapse
No announcement yet.

Find and delete that complete row

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Find and delete that complete row



    Find 12 in the sheet if 12 is found then delete that complete row

  • #2
    Code:
    ActiveSheet.UsedRange.Find(12,,,1).EntireRow.Delete Shift:=xlUp

    Comment


    • #3
      I don't want to delete 1 by 1
      i have to run the code once and all the row that contains 12 should be deleted i want that

      Comment


      • #4
        Code:
        Sub Delete_All_Twelves()
            Dim rCell As Range
            Dim lVal As Long
            Dim rngVal As Range
            Dim rng As Range
            Set rng = ActiveSheet.UsedRange
            Set rngVal = Nothing
            For Each rCell In rng
                If rCell.Value = 12 Then
                    If rngVal Is Nothing Then
                        Set rngVal = rCell
                            Else
                        Set rngVal = Union(rngVal, rCell)
                    End If
                End If
            Next
            rngVal.EntireRow.Delete Shift:=xlUp    'Select
            Set rCell = Nothing
        End Sub

        Comment


        • #5
          On a larger range, this is probably considerably faster.
          Code:
          Sub Replace_And_Delete()
              With ActiveSheet.UsedRange
                  .Replace What:=12, Replacement:="", Lookat:=xlWhole
                  .SpecialCells(4).EntireRow.Delete Shift:=xlUp
              End With
          End Sub
          Last edited by jolivanes; February 6th, 2018, 04:36. Reason: spelling

          Comment


          • #6
            And if you have rows where there would be a 12 on more than one occasion, you might have to use a looping code.
            Code:
            Sub Delete_With_Looping()
            Dim i As Long, ii As Long, lc As Long, lr As Long
            lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row
            lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
            Application.ScreenUpdating = False
                For i = 1 To lc
                    For ii = lr To 1 Step -1
                        If Cells(ii, i).Value = 12 Then Cells(ii, i).EntireRow.Delete Shift:=xlUp
                    Next ii
                Next i
            Application.ScreenUpdating = True
            End Sub

            Comment


            • #7


              Thnx

              Comment

              Working...
              X