Delete Rows with Small Values in 1 Column

[z3115]

Hello,

I need a macro that will delete rows based on a value in a specific column. I know this is not an uncommon code, but so far nothing I've found has worked with the little variations I have:

1) I want the number threshold on which to delete the rows to be a cell reference (i.e. so the user can change it based on what they want to see).

2) It would be an absolute value threshold (as in, if the user puts 100, it would delete all rows with a value of -100 to 100 in column B)

3) I need the first 2 rows to be ignored (they have titles, headers, etc, but the data is NOT formatted as a table).


I realize this is a specific request, so thank you so much for the help!

Edit:

This seems to be a good starting point, just doesn't do absolute value or ignore the first rows:

<code style="margin: 0px; padding: 0px; border: 0px; font-size: 14px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit;">[COLOR=#00008B]Sub[/COLOR] DeleteRows()    Application.ScreenUpdating = [COLOR=#800000]False[/COLOR]    Application.Calculation = xlCalculationManual    [COLOR=#00008B]Dim[/COLOR] i [COLOR=#00008B]As[/COLOR] [COLOR=#00008B]Long[/COLOR]    [COLOR=#00008B]For[/COLOR] i = Range([COLOR=#800000]"A"[/COLOR] & Rows.Count).[COLOR=#00008B]End[/COLOR](xlUp).Row [COLOR=#00008B]To[/COLOR] [COLOR=#800000]1[/COLOR] [COLOR=#00008B]Step[/COLOR] -[COLOR=#800000]1[/COLOR]        [COLOR=#00008B]If[/COLOR] [COLOR=#00008B]Not[/COLOR] (Range([COLOR=#800000]"C"[/COLOR] & i).Value < [COLOR=#800000]5000[/COLOR]) [COLOR=#00008B]Then[/COLOR]            Range([COLOR=#800000]"C"[/COLOR] & i).EntireRow.Delete        [COLOR=#00008B]End[/COLOR] [COLOR=#00008B]If[/COLOR]    [COLOR=#00008B]Next[/COLOR] i    Application.Calculation = xlCalculationAutomatic    Application.ScreenUpdating = [COLOR=#800000]True[/COLOR] </code></pre>[COLOR=#00008B][FONT=Consolas]End[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] [/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]Sub[/FONT][/COLOR]

 

[z3115]

Sorry first code got messed up:

[CODE]
Sub DeleteRowsThreshold()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim i As Long
    For i = range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
        If Not IsNumeric(range("B" & i).Value) Or range("B" & i).Value < 5000 Then
            range("B" & i).EntireRow.Delete
        End If
    Next i
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

 

[z3115]

Edit again...I now have everything figured out except how to ignore the first few rows, thoughts?

 

[z3115]

Anddd I've figured out my own issue, but for anyone else, the above code will work, you just need to change the "If Not" line to

If Not IsNumeric(range("B" & i).Value) Or Abs(range("B" & i).Value) < Sheets("Instructions").range("G7").Value Then

Where G7 is the cell you are using to set the limit. Then adjust the number in between To and Step in the 5th line to pick which row the macro should start working (so since I wanted it to leave the first 2 rows alone, I changed the 1 to a 3).