Delete entire rows if ...: how to speed up the process?

[Nelson78]

Hello everybody.

I have 500k rows to check.
If cell "H" is not beginning for "CODE", then delete entire row.

How You can imagine, with a such amount of rows the process takes too long.

How can I speed up? May be with a REGEX strategy?

Sub deleterows()

Dim rw As Long

    With Worksheets(1)
        For rw = .Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
            If Not Cells(rw, "H").Value Like "CODE*" Then
                .Rows(rw).EntireRow.Delete
            End If
        Next rw
    End With

End Sub

 

[Fluff]

How about

Sub nelson78()
   With worksheets(1)
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:H1").AutoFilter 8, "<>code*"
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

 

[Nelson78]

How about

Sub nelson78()
   With worksheets(1)
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:H1").AutoFilter 8, "<>code*"
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

Something wrong.

I have to write code or CODE?

Anyway, error 1004 object-defined error or application-defined on the following line

.AutoFilter.Range.Offset(1).EntireRow.Delete

 

[Fluff]

Are you trying to run this on the first sheet in the workbook?
Also do you have a header row in row 1?

Autofilter is not case sensitive so it doesn't matter if you use CODE* or code*

 

[Nelson78]

Are you trying to run this on the first sheet in the workbook?
Also do you have a header row in row 1?

Autofilter is not case sensitive so it doesn't matter if you use CODE* or code*

There is only 1 sheet in the workbook.
Columns are populated from A to P.
In row 1 yes, I have a header row.

 

[Fluff]

Is your data in a proper table?

 

[Nelson78]

Is your data in a proper table?

The question is not so easy for me.

Anyway: the data comes from an export that produce a csv file, then I select column A and convert Text to Columns, then save in .xlsx format.

 

[Fluff]

How about

Sub nelson78()
   Dim UsdRws As Long
   
   With Worksheets("dup")
      If .AutoFilterMode Then .AutoFilterMode = False
      UsdRws = .Range("A" & Rows.Count).End(xlUp).Row
      .Range("A1:H" & UsdRws).AutoFilter 8, "<>code*"
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

 

[Nelson78]

How about

Sub nelson78()
   Dim UsdRws As Long
   
   With Worksheets("dup")
      If .AutoFilterMode Then .AutoFilterMode = False
      UsdRws = .Range("A" & Rows.Count).End(xlUp).Row
      .Range("A1:H" & UsdRws).AutoFilter 8, "<>code*"
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

The workbook I was using before was in COMPATIBILITY MODE.
Than, I saved again in .xls format to inhibit that mode.

Now, processing your macro, it works fine (350597 record elaborated in 3 minutes).

Do you have any idea about the error I firstly mentioned?

 

[Fluff]

Glad to hear it's working, but I've no idea why you got that error before.