VBA to delete large number of rows

[JSAUS]

Hello!

I need to search column "L" for the item code "126000" and then delete the entire row if that item number is present. Is there a faster way than I'm currently doing it (code below) as the spreadsheet has 15,000+ rows for it to iterate through.....
Is there a way to filter then delete then unfilter?

Sub DeleteRowWithCode()
Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "L").Value) = "126000" Then
Cells(i, "A").EntireRow.Delete
End If
Next i

End Sub


Thank you!

 

[JSAUS]

Perhaps if you just copy the range involved & post that (with an indication of where your data starts row/column, and table name).

Thanks so much.
Hopefully this data extract helps? I had to delete most of the data out because even the extract was too large to post.
The table is called "DataExport". The headers are all just in row 1. Used columns goes to column AB. Used rows will vary depending on the month/export. Any row with Product Code of "126000" (Column L) needs the entire row deleted.

Site

Company

Company Code

Identifier

Type

Ticket

Delivery Docket

JobCode

Job Name

DateOut

Loaded Date

ProductCode

Product

Delivery Zone

Type2

Net

Qty

Dist

Ex Tax

Tax

Total

Identifier2

Maximum

Minimum

Target

Date

Shift

Daily Shift

61​

*BT29

4/01/2023 7:00​

129306​

1​

17​

*BT29

4/01/2023 7:00​

126000​

1​

97​

*BT29

4/01/2023 7:00​

129306​

1​

15​

*BT29

4/01/2023 7:00​

126000​

1​

76​

7KW0R86

4/01/2023 10:10​

112751​

0.7​

65​

7KW0R86

4/01/2023 10:10​

126000​

1​

88​

7KW0R86

4/01/2023 10:10​

112751​

0.7​

81​

*BT28

24/01/2023 10:41​

129306​

1​

88​

*BT28

24/01/2023 10:41​

126000​

1​

17​

*BT28

24/01/2023 10:41​

129306​

1​

24​

*BT28

24/01/2023 10:41​

126000​

1​

3​

BR4391

24/01/2023 7:01​

130715​

17.96​

0​

38​

BR4391

24/01/2023 7:01​

126000​

1​

95​

BR4391

24/01/2023 7:01​

130715​

17.96​

0​

48​

BR4391

24/01/2023 7:01​

126000​

1​

51​

7KW0R82

6/01/2023 15:30​

112751​

0.25​

52​

7KW0R82

6/01/2023 15:30​

126000​

1​

26​

7KW0R82

6/01/2023 15:30​

112751​

0.25​

41​

7KW0R82

6/01/2023 15:30​

126000​

1​

82​

7KW0R90

4/01/2023 7:00​

126000​

1​

72​

7KW0R90

4/01/2023 7:00​

144863​

7.42​

97​

7KW0R90

4/01/2023 7:00​

126000​

1​

56​

7KW0R90

4/01/2023 7:00​

144863​

7.42​

29​

7KW0R92

5/01/2023 7:00​

126000​

1​

49​

7KW0R92

5/01/2023 7:00​

144863​

6.25​

66​

7KW0R92

5/01/2023 7:00​

126000​

1​

10​

7KW0R92

5/01/2023 7:00​

144863​

6.25​

78​

7KW0R88

6/01/2023 8:55​

131236​

0.42​

9​

7KW0R88

6/01/2023 8:55​

126000​

1​

20​

7KW0R88

6/01/2023 8:55​

131236​

0.42​

68​

7KW0R88

6/01/2023 8:55​

126000​

1​

12​

7KW0R82

6/01/2023 13:50​

131236​

0.6​

1​

7KW0R82

6/01/2023 13:50​

126000​

1​

94​

7KW0R82

6/01/2023 13:50​

131236​

0.6​

5​

7KW0R82

6/01/2023 13:50​

126000​

1​

52​

*BT31

10/01/2023 10:30​

131236​

1.33​

2​

*BT31

10/01/2023 10:30​

126000​

1​

31​

*BT31

10/01/2023 10:30​

131236​

1.33​

1​

*BT31

10/01/2023 10:30​

126000​

1​

68​

*BT31

12/01/2023 10:50​

126000​

1​

2​

*BT31

12/01/2023 10:50​

112751​

0.17​

32​

*BT31

12/01/2023 10:50​

126000​

1​

83​

*BT31

12/01/2023 10:50​

112751​

0.17​

100​

*BT31

12/01/2023 13:42​

131236​

1.45​

87​

*BT31

12/01/2023 13:42​

132483​

158.64​

18​

*BT31

12/01/2023 13:42​

126000​

1​

18​

*BT31

12/01/2023 13:42​

131236​

1.45​

 

[kevin9999]

EDITED
It's a lot trickier with tables (as opposed to ranges) when it comes to using the array method. I would go for the Filter/delete method instead. Try the following on a copy of your file:

Sub Delete_By_Filter()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<~~ change as needed
    With ws
        With .ListObjects("DataExport").DataBodyRange
            .AutoFilter 12, "126000"
            If ws.UsedRange.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
                .EntireRow.Delete
            End If
        End With
        .ShowAllData
    End With
End Sub