Sorting rows based on value in locked cell

[HJA14]

Hi,

I would like to sort my excel file based on the value in cell BW1.
- If this cell is empty, I want the rows to be sorted based on column N from highest to lowest.
- If the cell is non-empty, I want to use the value of this cell to exclude all rows that have a value that is smaller than the value in cell BW1. In order to achieve this the value of a specific cell in column "M" must be compared to the value of cell BW1. Afterwards, we sort again the file based on column N from highest to lowest

I have written the following code

Application.ScreenUpdating = FalseDim LastCellData
Dim LastRow


Sheets("Data").Select
Range("A2").Select
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Clear


If Range("BW1").Value = "" Then
    Sheets("Data").Select
   
    ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "N1:N & LastRow"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Data").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Else
    Sheets("Data").Select
    
    ActiveSheet.Range("A1:BV" & LastRow).AutoFilter Field:=20, Criteria1:=">=" & Range("BW1").Value _
    , Operator:=xlAnd
    'The above command doesn't work properly
    ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "N1:N & LastRow"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Data").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End If




Sheets("Overview").Select
Application.ScreenUpdating = True

However, this macro hides all rows when the cell is non-empty. Help is much appreciated!!

 

[MartyS]

Hi!

First, there's a problem with your code here:

    ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "N1:N & LastRow"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal

It should read:

    ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "N1:N" & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal

What's changed?

Where you've added the range for the KEY.. you have

"N1:N & LastRow"



You've included the variable inside the quotation marks


It should read:

"N1:N" & LastRow



NB: Both instances of this needs to be changed



The line of code that you say doesn't work is:

ActiveSheet.Range("A1:BV" & LastRow).AutoFilter Field:=20, Criteria1:=">=" & Range("BW1").Value, Operator:=xlAnd

seems to work fine!

It is FILTERING your data (not sorting) based on whatever is in Field 20 (Presumably COLUMN [T]) where the values in that column are greater than or equal to the number in cell [BW1]

After it's filtered the data, it then SORTS on column [N] (descending)

At the end of the macro, a different worksheet is selected called Overview.




Here's the amended routine:

Sub Test()

    Application.ScreenUpdating = False
    Dim LastCellData
    Dim LastRow
    
    
    Sheets("Data").Select
    Range("A2").Select
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Clear
    
    
    If Range("BW1").Value = "" Then
        Sheets("Data").Select
       
        ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Add Key:=Range( _
            "N1:N" & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Data").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Else
        Sheets("Data").Select
        
        ActiveSheet.Range("A1:BV" & LastRow).AutoFilter Field:=20, Criteria1:=">=" & Range("BW1").Value, Operator:=xlAnd
        
        ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Add Key:=Range( _
            "N1:N" & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Data").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
    End If
    
    Sheets("Overview").Select
    Application.ScreenUpdating = True


End Sub

Hope this helps?