Macro to delete rows below a certain value

[MisterEvans]

Hi,

I have a large set of data and need to run a macro that sorts "column CN", then removes the data where the value in column CN is <50,000. I can do the sorting but not the deleting: please could you help?

The macro so far with the sorting ....

Sub Macrox()
'
' Macrox Macro
'
' Keyboard Shortcut: Ctrl+x
'
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
("CN1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("$A$1:$MT$51").AutoFilter Field:=92, Criteria1:="<=50000" _
, Operator:=xlAnd
End Sub


Thanks in advance!!

 

[vds1]

Hello, Welcome to mrexcel.com

I am assuming the data is from column A to CN, if not please change column name in .SetRange Range("A1:CN" & LastRow) as per your sheet on the code.

Sub Macro1()




Dim LastRow As Long
Dim i As Integer




Sheets("Sheet1").Select
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("CN2").Select
With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("CN2:CN" & LastRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange Range("A1:CN" & LastRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
End With


For i = LastRow To 1 Step -1
                If Cells(i, 92) <= 50000 Then
                    Rows(i).Delete Shift:=xlUp
                End If
Next i
Range("A1").Select
End Sub

 

[MisterEvans]

That does the trick !! Thanks indeed for your speedy and so helpful reponse