vba to delete rows based on a cell value

[cjcass]

Hi,

I'm looking for some vba that will delete all rows in Sheet1 that contain a value in Column CL that is less than the value in cell CG3. It needs to delete rows from CL9 down and the last row in CL is variable. Column CL may contain thousands of rows of data so I guess I'm after a 'non-looping' solution. The value in CG3 will be a percentage, so if it is set to, say 50%, then all rows that have a value in Column CL that is less than 50% will get deleted.

Any help much appreciated.

 

[bebo021999]

Try:

Option Explicit
Sub delete()
Dim lr&
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
lr = Cells(Rows.Count, "CL").End(xlUp).Row
    With Range("CL8:CL" & lr)
        .AutoFilter field:=1, Criteria1:="<" & Range("CG3").Value
        .SpecialCells(xlCellTypeVisible).EntireRow.delete
    End With
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

 

[alansidman]

try this:

Option Explicit

Sub foo()
Application.ScreenUpdating = False
Dim i As Long, lr As Long
Dim r As Range
Set r = Range("CG3")
lr = Cells(Rows.Count, "CL").End(xlUp).Row
For i = lr To 9 Step -1
If Range("CL" & i) < r Then
Range("CL" & i).EntireRow.Delete
End If
Next i
Application.ScreenUpdating = True
MsgBox "completed"
End Sub

 

[cjcass]

Thank you both for your time and help with this. Both solutions worked fine.
That said, when targetting 15,000 rows the autofilter solution was faster.
Many thanks again