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!

 

[mikerickson]

Dim i As Long

With Sheets("Sheet1").Range("J:J")
    For i = .Cells(Rows.Count,1).End(xlup).Row to 13 Step - 1
        If .Cells(i, 1).Value = 2 Then
            .Cells(i,1).EntireRow.Range("A1:X1").Delete Shift:=xlUp
        End If
    Next i
End With

 

[mike00]

Hi thanks for the reply. I have over 60,000 data and the code doesn't seem to work

 

[My Aswer Is This]

Explain again what you want please.

Your saying if I understand.

Delete part of the row if column "J" has the value "2"
Starting in row 13
You do not want the entire row deleted is that correct?

 

[MARK858]

Try changing this line in Mikes code

If .Cells(i, 1).Value = 2 Then

to

If .Cells(i, 10).Value = 2 Then

 

[mike00]

Yes because after column X I have data that I do not want to disturb. So only delete the row from column A to column X if column J contains '2'.
My data starts from row 13. I need to have a message box telling how many rows have been removed. I have over 60,000 data, mikerickson's code took very long time to run

 

[My Aswer Is This]

edit

 

[dfsobral]

with 60000 rows mikes code will take forever

try sorting the table and then delete one block of rows from first time 2 appears till last time
even doing this manually would be faster than mikes code and you could count how many rows you selected to delete

the problem is if the relative position of each row maters or have implications with other data

 

[My Aswer Is This]

Try this:

Sub My_Filter()
'Modified 1-15-18 9:00 PM EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
        Lastrow = Sheets(1).Cells(Rows.Count, "J").End(xlUp).Row
        With Range("J13:J" & Lastrow)
            .AutoFilter 1, "2"
            .Offset(0, -9).Resize(, 15).Delete xlShiftUp
        End With
    ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = False
End Sub

 

[mike00]

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