Find and delete that complete row

agog1 · Feb 4th 2018

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

jolivanes · Feb 5th 2018

Code

ActiveSheet.UsedRange.Find(12,,,1).EntireRow.Delete Shift:=xlUp

agog1 · Feb 5th 2018

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

jolivanes · Feb 5th 2018

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

Display More

jolivanes · Feb 5th 2018

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

jolivanes · Feb 5th 2018

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

Display More

agog1 · Feb 13th 2018

Thnx

Find and delete that complete row

Participate now!

Share