PDA

View Full Version : Quickly delete rows with value > 80%



agnesz
10-30-2008, 01:12 PM
I have a very large range of data, about 20000 rows give or take starting from column b to fw. I need a macro to quickly delete rows that have a value greather than 80% in column FW. Please help!
Thanks...
az

mdmackillop
10-30-2008, 01:49 PM
Probably easiest with a helper column FX in this case; adjust to suit.

Option Explicit
Sub DelRows()
Dim Rw As Long, Find80 As Long
On Error GoTo Exits
Application.Calculation = xlCalculationManual
Rw = Cells(Rows.Count, "FW").End(xlUp).Row
Range("FX1") = 1
Range(Cells(1, "FX"), Cells(Rw, "FX")).DataSeries Rowcol:=xlColumns, Step:=1

Columns("B:FX").Sort Key1:=Range("FW1"), Order1:=xlDescending, Header:=xlNo _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Find80 = Application.Match(0.8, Columns("FW"), -1)

Rows(1 & ":" & Find80).Delete

Columns("B:FX").Sort Key1:=Range("FX1"), Order1:=xlAscending, Header:=xlNo _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Columns("FX").ClearContents
Exits:
Application.Calculation = xlCalculationAutomatic
End Sub

mdmackillop
10-30-2008, 02:03 PM
Just a thought.
You may want to turn off automatic calculation to speed things up if you have a lot of formulae in your sheet.