Delete rows based on range of values found in Column A

[oliviar]

Hi Guys,
I'm SO new to this, I don't even really understand how to put macros into the Excel coding section.
I want to delete all the rows that show anything BUT a value between 300 and 400 in column A.
I was trying to modify some of the other 'delete row' codes that have been supplied, but no luck.

 

[jeffreybrown]

Hello and welcome to the board,

See if this works for you.

Enter this code in a standard module

Take a look here about where to paste code

Sub DelOliviar()
    Dim LR As Long, i As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    For i = LR To 2 Step -1
        If Range("A" & i).Value >= 300 And Range("A" & i).Value >= 400 Then Rows(i).Delete
    Next i
End Sub

 

[jeffreybrown]

This one might be even faster depending on how big your dataset is.

Sub FilterDeleteOliviar()
    With Range("A1", Range("A" & Rows.Count).End(xlUp))
        .AutoFilter Field:=1, Criteria1:=">=300", Operator:=xlAnd, Criteria2:="<=400"
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
End Sub

The difference is the first code loops through all cells in Column A and the latter filter and deletes in one swipe.

 

[oliviar]

Yes this first one is taking a really long time. I will see what happens with the second one once this first version finishes its thing. Thank you so much though.

 

[oliviar]

Sorry Jeff, Its just not working. Nothing has been deleted. I have tried both.
Row 1 has headings in it. Could that be the problem?

 

[jeffreybrown]

Heading in row 1 are not a problem. Do you want to keep everything between 300 and 400 or delete between 300 and 400?

Before Macro

Excel Workbook
	A
1	Data
2	223
3	375
4	444
5	325
Sheet2

After Macro

Excel Workbook
	A
1	Data
2	223
3	444
Sheet2

Macro

Sub FilterDeleteOliviar()
    With Range("A1", Range("A" & Rows.Count).End(xlUp))
        .AutoFilter Field:=1, _
                    Criteria1:=">=300", _
                    Operator:=xlAnd, _
                    Criteria2:="<=400"
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
End Sub

 

[oliviar]

Keep everything between 300 and 400.
Well technically everything between 300 and 399. Also including "300".

 

[jeffreybrown]

Keep everything between 300 and 400.
Well technically everything between 300 and 399. Also including "300".

Change to:

Sub FilterDeleteOliviar()
    With Range("A1", Range("A" & Rows.Count).End(xlUp))
        .AutoFilter Field:=1, _
                    Criteria1:="<300", _
                    Operator:=xlOr, _
                    Criteria2:=">399"
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
End Sub