Delete entire row if Colunm contains number between two values

[MPH88]

I have a table with product codes in Column H. I run the macro below to delete rows with product codes that I do not want to track. This bit of code uses and array to delete rows that are not in the array. It works without problems.

    Dim FirstRow As Long
    Dim LastRow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
    Dim Counties As Variant
    Dim Classes As Variant
    
    Dim JoinedClasses As String
    Dim Delimiter As String
    Delimiter = "#"
    
    Classes = Array("4511", "5028", "5057", "5184", "5213", "5403", "5432", "5473", "5474", "5482", "6218", "6220", "9008")
    
    JoinedClasses = Delimiter & Join(Classes, "#") & Delimiter
    
    
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With




    With ActiveSheet
    .Select


        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView.DisplayPageBreaks = False


        FirstRow = .UsedRange.Cells(1).Row
        LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row


        For Lrow = LastRow To FirstRow + 1 Step -1


            With .Cells(Lrow, "H")
                If InStr(1, JoinedClasses, Delimiter & .Value & Delimiter, vbTextCompare) = 0 Then
                    .EntireRow.Delete
                End If

               
            End With


        Next Lrow



    End With

The same table has additional data in Column J. This data is numeric (between 1 and 999). I want to add additional VBA code to my macro and delete additional rows based on the values in Column J. But, instead of specifying an array with 5 or 10 product codes to keep, I want to keep rows that contain ranges of numbers (like… Keep rows that contain values between 90 and 250; or, keep rows that contain values greater than or equal to 125).


Any suggestions?

 

[DanteAmor]

Try this

Sub delete_rows()
    Dim FirstRow As Long
    Dim LastRow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
    Dim Counties As Variant
    Dim Classes As Variant
    
    Dim JoinedClasses As String
    Dim Delimiter As String
    Delimiter = "#"
    
    Classes = Array("4511", "5028", "5057", "5184", "5213", "5403", "5432", "5473", "5474", "5482", "6218", "6220", "9008")
    
    JoinedClasses = Delimiter & Join(Classes, "#") & Delimiter
    
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With


    With ActiveSheet
        .Select
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView.DisplayPageBreaks = False


        FirstRow = .UsedRange.Cells(1).Row
        LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row


        For Lrow = LastRow To FirstRow + 1 Step -1
            With .Cells(Lrow, "H")
                If InStr(1, JoinedClasses, Delimiter & .Value & Delimiter, vbTextCompare) = 0 Then
                    .EntireRow.Delete
                End If
            End With
[COLOR=#0000ff]            With .Cells(Lrow, "J")[/COLOR]
[COLOR=#0000ff]                If Not (.Value >= 90 And .Value <= 250) Then[/COLOR]
[COLOR=#0000ff]                    .EntireRow.Delete[/COLOR]
[COLOR=#0000ff]                End If[/COLOR]
[COLOR=#0000ff]            End With[/COLOR]
        Next Lrow
    End With
End Sub

 

[MPH88]

Muchas gracias, Dante! Works perfectly.

 

[DanteAmor]

Con mucho gusto.
Thanks for the feedback.