Sorting rows based on value in locked cell

HJA14

Board Regular
Joined
Apr 12, 2016
Messages
60
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

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!!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi!

First, there's a problem with your code here:
Code:
    ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "N1:N & LastRow"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal



It should read:

Code:
    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:


Code:
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:
Code:
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?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top