Forum Index | Search | Register (New User) | Login (Existing User) | Who's Online | FAQ | Main Site

Technical Discussion
  >> Windows Issues Previous thread View all threads Next thread


Register (or login) on our website and you will not see this ad.


Pages in this thread: 1 | 2 | 3 | (show all)   Print Thread
Standard User deleted
(deleted) Thu 15-May-14 11:09:18
Print Post

Excel help


[link to this post] 		 
Cells A36 to cell A50 have names in B36 to B50 have their values (which are imported for other cells in worksheet).
The values change and I want to automatically sort them by value highest to lowest

What's the best way to do this?
Standard User ian72
(knowledge is power) Thu 15-May-14 12:00:23
Print Post

Re: Excel help


[re: deleted] [link to this post] 		 
Only way I can think to do this is via a macro.

Be careful though is doing autorun macros wrongly could leave you with a very unresponsive spreadsheet.

Right click on the sheet tab and select "View Code". Paste this code in:

Text
1
23
45
67
89
1011
1213
1415
1617
1819
2021
2223
2425
26
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range 
    ' The variable KeyCells contains the cells that will    ' cause an alert when they are changed.
    Set KeyCells = Range("B36:B50")    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _           Is Nothing Then
         ' Sort cells.
        Range("A36:B50").Select        ActiveWorkbook.Worksheets("Sheet1").AutoFilter.sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").AutoFilter.sort.SortFields.Add Key:=Range _            ("B36:B50"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal        With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.sort
            .Header = xlYes            .MatchCase = False
            .Orientation = xlTopToBottom            .SortMethod = xlPinYin
            .Apply    End With
           End If
End Sub
Standard User deleted
(deleted) Thu 15-May-14 16:56:01
Print Post

Re: Excel help


[re: ian72] [link to this post] 		 
I must be doing something wrong as can't get it to work.

PS thanks for taking the time to help.

Edited by deleted (Thu 15-May-14 16:56:36)


Register (or login) on our website and you will not see this ad.

Standard User ian72
(knowledge is power) Thu 15-May-14 17:00:29
Print Post

Re: Excel help


[re: deleted] [link to this post] 		 
What version of Excel? I did it in 2010 but would think it should be ok in others.

The other thing you can do is record a macro that selects the data set and does the sort. Then copy that macro and copy it into the code provided here replacing the MsgBox line.

That way you will have the right code for doing the sort and by building it yourself on your spreadsheet it should bypass any issues (that was how I created it in the first place).

PS - failing that I can send you contact details and you could send me the spreadsheet and I could check it out and see if I can see where the problem is.

Edited by ian72 (Thu 15-May-14 17:01:13)
Standard User deleted
(deleted) Thu 15-May-14 17:15:11
Print Post

Re: Excel help


[re: ian72] [link to this post] 		 
Thanks, It's a multi tab when I view code it says sheet 2, do I have to change it on the macro?
Standard User ian72
(knowledge is power) Thu 15-May-14 17:26:49
Print Post

Re: Excel help


[re: deleted] [link to this post] 		 
Yes. If the tab is not named sheet1 then you will need to change those references in the code to whatever the tab is called. And whilst I think of it if the code isn't running at all you may need to save as a macro enabled spreadsheet (2010 and above increased security for macros).
Standard User deleted
(deleted) Thu 15-May-14 17:36:27
Print Post

Re: Excel help


[re: ian72] [link to this post] 		 
The sheet is called 2014 and I activated macros (the excel icon changed) I'm using 2010

Can you see any errors I made?

1
23
45
67
89
1011
1213
1415
1617
1819
2021
2223
24
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    
    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("B36:B50")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
    Is Nothing Then
         ' Sort cells.
        Range("A36:B50").Select
        ActiveWorkbook.Worksheets("2014").AutoFilter.Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("2014").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("B36:B50"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
            With ActiveWorkbook.Worksheets("2014").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
            End With
           End If
End Sub
Standard User ian72
(knowledge is power) Thu 15-May-14 22:33:26
Print Post

Re: Excel help


[re: deleted] [link to this post] 		 
Sorry, when I created the code I had setup a filter and the code was doing a sort based on that. To do it without a filter give this a try (it should work as my test spreadsheet is now setup about the same as yours):

Text
1
23
45
67
89
1011
1213
1415
1617
1819
2021
2223
2425
26
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range    
    ' The variable KeyCells contains the cells that will    ' cause an alert when they are changed.
    Set KeyCells = Range("B36:B50")    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _    Is Nothing Then
    ' Sort cells.        CurrCell = ActiveCell.Address
        Range("A36:B50").Select        ActiveWorkbook.Worksheets("2014").sort.SortFields.Clear
        ActiveWorkbook.Worksheets("2014").sort.SortFields.Add Key:=Range("B36:B50"), _            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("2014").sort            .SetRange Range("A36:B50")
            .Header = xlGuess            .MatchCase = False
            .Orientation = xlTopToBottom            .SortMethod = xlPinYin
            .Apply        End With
        Range(CurrCell).Select    End If
End Sub

Edited by ian72 (Fri 16-May-14 07:03:35)
Standard User ian72
(knowledge is power) Thu 15-May-14 22:55:17
Print Post

Re: Excel help


[re: ian72] [link to this post] 		 
Another question in case that last option doesn't work... What is actually in the cells in the B column? Is it actual numbers or is it formulas pointing to other cells. If it is the latter then the sort won't work because the formulas won't move in the sort. It also won't trigger as the cell doesn't change if it is a formula just the value that is in it.

So, if it doesn't work can you post a sample of what is in a cell (say the formula/contents of B36).

EDIT : OK, have found a way to potentially do it with formulas but it could be clunky depending on the spreadsheet. Hopefully you don't have formulas in the column but if you do then we can try something a little more complicated.

Edited by ian72 (Thu 15-May-14 23:25:10)
Standard User deleted
(deleted) Fri 16-May-14 07:59:30
Print Post

Re: Excel help


[re: ian72] [link to this post] 		 
Currently
=$B$18 is in cell B36 =$B$19 in B37 down to =$B$32 in cell B50
Pages in this thread: 1 | 2 | 3 | (show all)   Print Thread

Previous thread View all threads Next thread
Jump to