Delete entire rows according to many values-CodePudding

I have a long excel file, from which I need to delete entire rows that contain certain values in the column A. The column contains values from [PAR001 to PAR0023247] and I need to delete the ones that fall within [PAR002537 To PAR005214] Which is the fastest way to do that?

CodePudding user response：

Try searching through the range for "PAR" and use the val() method to compare the varying lengths of the integers without having to search for a "0" which could be part of the integer on the right.

It's important to count through the range backwards. Also, if you have any cells that start with "PAR" above the list in question, there could be issues.

This worked for me. I hope it works for you.

Public Sub DeleteRows()

Dim arr As Range
Dim tester As String
Dim NumElements As Long


Set arr = Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp))

NumElements = arr.Cells.Count
For i = NumElements To 1 Step -1
    If Left(arr(i), 3) = "PAR" Then
        tester = Right(arr(i), Len(arr(i)) - 3)
        If Val(tester) >= 2537 And Val(tester) <= 5214 Then
            Cells(i, 1).EntireRow.Delete
        End If
    End If
Next i

        
End Sub

CodePudding user response：

To be honest, I think @haplo76 answer is pretty good.

My answer will work ONLY if your data is sorted ascending by those PARxxxx values. If not, then use @haplo76 answer

The only advantage of this solution is that you don't need to loop, and delete all rows at a single line of code.

In my test I deleted rows [PAR03 to PAR010]

Sub TEST()
Dim i_row As Long
Dim e_row As Long


'In e_row you want to match the value AFTER the last one you want to to delete
' so if you want to delete from PAR003 to PAR010
' i_row will search for PAR003 but e-row need to search for PAR011!!!

With Application.WorksheetFunction
    i_row = .Match("PAR003", Range("A:A"), 0)
    e_row = .Match("PAR011", Range("A:A"), 0)
End With

Range("A" & i_row & ":A" & e_row).Delete

End Sub

After executing code i get:

Another advantage is that you can convert easily this sub into a generic one asking for just the PARxx values to delete:

Sub TEST()
DELETE_ROWS "PAR003", "PAR011" 'this will delete [PAR003 to PAR010]
End Sub

Sub DELETE_ROWS(ByVal ini_val As String, end_val As String)
Dim i_row As Long
Dim e_row As Long

With Application.WorksheetFunction
    i_row = .Match(ini_val, Range("A:A"), 0)
    e_row = .Match(end_val, Range("A:A"), 0)
End With

Range("A" & i_row & ":A" & e_row).Delete

End Sub

But as I said at first, this will work only if your data is sorted in column A and ascending order. Also, if you type a "PAR00xx" value not found, it will raise an error.