Delete the entire row until column X if value '2' is found in column J?

[mike00]

Does anyone know how to delete the entire row until column x if value '2' is found in column J?
How the macro should look like? My data starts from row 13th and I require a message box to tell how many rows have been removed. Thank you in advance!

 

[My Aswer Is This]

Sorry. I do not know how to get a answer to how many rows were deleted. Did the main script do what you want?

@MyAswerIsThis How to insert message box in your code? Telling how many rows have been removed

 

[MARK858]

Sorry. I do not know how to get a answer to how many rows were deleted. Did the main script do what you want?

@ M.A.I.T, to get the visible rows count of a filter use Subtotal(103,Range) as per the below but you could do a count of the 2's beforehand.

Sub My_Filter()
    'Modified 1-15-18 9:00 PM EST
    Application.ScreenUpdating = False
    Dim i As Long, x As Long
    Dim Lastrow As Long
    
    Lastrow = Sheets(1).Cells(Rows.Count, "J").End(xlUp).Row

    With Sheets(1).Range("J12:J" & Lastrow)
        .AutoFilter 1, "2"
        [COLOR="#FF0000"]x = Application.WorksheetFunction.Subtotal(103, Sheets(1).Range("J13:J" & Lastrow))[/COLOR]
        .Offset(1, -9).Resize(.Rows.Count - 1, 24).Delete xlShiftUp
    End With

    MsgBox x
    ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = False
End Sub

Bigger problem I think is if you number column Y from 1 to the last row I think you will find that it is deleting the entire rows.

Perhaps an alternative might be

Sub DelColJ()
    Dim x As Long, Lastrow As Long

    Lastrow = Sheets(1).Cells(Rows.Count, "J").End(xlUp).Row
    x = Application.CountIf(Sheets(1).Range("J13:J" & Lastrow), "2")


    Sheets(1).Range("J13:J" & Lastrow).Replace "2", "#N/A", xlWhole, , True, False, False
    On Error Resume Next
    Intersect(.SpecialCells(xlConstants, xlErrors).EntireRow, Range("A:X")).Delete
    On Error GoTo 0


    MsgBox x
End Sub

Btw, apologies to MikeRickson earlier for not reading his code properly and so making an incorrect post

 

[MARK858]

Edited my last post to remove the With statement when I still needed it, teach me not to retest
Too late to re-edit

Sub DelColJ()
    Dim x As Long, Lastrow As Long

    Lastrow = Sheets(1).Cells(Rows.Count, "J").End(xlUp).Row
    x = Application.CountIf(Sheets(1).Range("J13:J" & Lastrow), "2")

    With Sheets(1).Range("J13:J" & Lastrow)
        .Replace "2", "#N/A", xlWhole, , True, False, False
        On Error Resume Next
        Intersect(.SpecialCells(xlConstants, xlErrors).EntireRow, Range("A:X")).Delete
        On Error GoTo 0
    End With

    MsgBox x
End Sub

 

[MARK858]

Sigh....

Sub DelColJ()
    Dim x As Long, Lastrow As Long

    Lastrow = Sheets(1).Cells(Rows.Count, "J").End(xlUp).Row
    x = Application.CountIf(Sheets(1).Range("J13:J" & Lastrow), "2")

    With Sheets(1).Range("J13:J" & Lastrow)
        .Replace "2", "#N/A", xlWhole, , True, False, False
        On Error Resume Next
        Intersect(.SpecialCells(xlConstants, xlErrors).EntireRow, [COLOR="#FF0000"]Sheets(1).[/COLOR]Range("A:X")).Delete
        On Error GoTo 0
    End With

    MsgBox x
End Sub