Quickly delete rows in a range containing certain value

[bran]

Hi, this is my first post so please let me know any mistakes.

I have a worksheet of 10,000 rows containing data seperated into multiple columns. I want to select all the cells in one column that don't contain a user input value and then delete all the rows containing those cells.

I can do this with a loop but it takes far too long. I'm really looking for any method that can quickly get rid of the unwanted rows that doesn't use loops.

 

[slay0r]

Hi VoG,

Is it possible to use this code to delete a range of say less than £500?

I've got 10000 lines of data and I Want the macro to delete all instances less than £500 (Both debits and credits) Where would I put this on your code, or anyone?! I'm guessing I change the W to AG in my case as that's the column I want it to look at?

 

[VoG]

Try with a copy of your sheet

Sub Del_x()
With Columns("AG")
    .AutoFilter field:=1, Criteria1:="<500", Criteria2:="<-500"
    .Resize(Rows.Count - 1).Offset(1).EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False
End Sub

 

[slay0r]

That seems to have deleted all of my minuses ABOVE 500 and kept all the ones below in the spreadsheet.

It's also not touched anything with a positive figure for some reason

My range goes between 22000 and -270,000 and I need to delete anything less than £500 and -£500, don't see how your formula hasn't worked Mr VoG

 

[VoG]

I think a loop might be better

Sub Del_x()
Dim i As Long, LR As Long
LR = Range("AG" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = LR To 2 Step -1
    If Abs(Range("AG" & i).Value) < 500 Then Rows(i).Delete
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[slay0r]

YES!

Absolute legend!

Any suggestions on some sites or info I can look at to figure out what all that code actually means and how it's working? That's extremely helpful, I want to use that again and again elsewhere!

Kind Regards
Jonathan

 

[VoG]

Take a look at Hiker's List http://www.mrexcel.com/forum/showpost.php?p=2676997&postcount=5

 

[slay0r]

You sir, are a legend!

Thanks very much for all your help, very much appreciated and sorry for thread jacking whoever made it!