Sort Dynamic Range - help please

[prowebcommerce]

I've looked everywhere and cannot work out the way to reference the sort key in my dynamic range. I believe it's relatively easy but my VBA still not up to it.

I have a dynamic range that I want to sort using a column within the range. The range is A18:AR25, refers to =OFFSET(BudgetEA!$A$1,0,0,COUNTA(BudgetEA!$A:$A),1)

I want to sort the range by column D, descending order (A-Z).

I can do it by Recording a macro, but I don't think that will take added or deleted rows into account.

Any help appreciated.

 

[Perpa]

prowebcommerce,

Have a look at this link...the code is annotated so you should be able to make the necessary changes to fit your data and sort column.

http://www.mrexcel.com/forum/excel-...s-surname-but-keep-full-name-single-cell.html

Perpa

 

[prowebcommerce]

Hi Perpa,

Thanks, I had looked at this and tried to adapt it as well, but I think this is different than working with a dynamic named range? I could not see where/how to make the correct reference to the sort column. As I said, my knowledge of VBA is still very new and not very good.

 

[Perpa]

prowebcommerce,
Here is the code you requested. I have not tested it since you have not provided sample data, but it should run. Come back if it does not and provide sample data we can check.
This code goes in a standard code module (Alt+F8, paste the code in the VB Editor window that opens, then close the Editor) and SAVE AS a macro enabled file.
Make a copy so don't lose anything.
Perpa

Sub SortMyList()
    'The following code alphbetically sorts on Column D
    'New data can be added to the bottom of the list and will be sorted  creating a'dynamic List')
    Dim LastARow As Integer      'I used column A to find the last row, change to suit and refelct change below
    
    LastARow = Range("A18").End(xlDown).Row  ' change to suit

    With Application  'This WITH statement keeps the screen from changing until sorting is done
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    'The following sorts based on column D values in 'Ascending' order A to Z. Change 'Ascending' to 'Decending' if desired
    Range("A18:AR" & LastARow).Select    'This selects the Range to be sorted in the helper column and the original column
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D18:D" & LastARow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort      'Change this to suit if your list is not on Sheet1
        .SetRange Range("A18:AR" & LastARow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("D18").Select     'I chose a cell at the top of the sorted range to end on, change to suit
    
    With Application  'With sorting done let the screen changes be shown
        .ScreenUpdating = True
        .EnableEvents = True
    End With
 
End Sub

 

[prowebcommerce]

Hi Perpa,
I take your point about sample data, normally I would but I don't really have any. None of my attempts worked out and I was working blind with negative results. I think I was remiss not providing better information, my apologies.

The problem I have is that the data is not contiguous. My workbook is a family budget. The Income & Expenses sheet is 3 lists (Bank), Income, Expenses) with totals for each. The layout is for cosmetic rather than practical purpose. New data (rows) are added by macro to the top of each list (not the bottom). The sort needs to rearrange based on the account code (column D). I would attach a sample sheet to illustrate the problem, but can't see how on this forum.

Your code works for a list where it is a single list. In my case it is not a single list, but a list in 3 separate parts. Each part will have additions & deletions (rows) and need sorting from time to time. Hence start & end rows will rarely be constant. I hope this is explains better. Anyway, thanks for sticking with me, I do appreciate it.

Ron S

 

[Perpa]

Ron S,
As with most things a picture is worth a 1000 words.
The link below shows how to provide a sample of your worksheet with instructions (see Posts #2 and #3).
Thanks to Peter_SSs, Jon von der Heyden , and Rory et al for their efforts.
https://www.mrexcel.com/forum/about-board/508133-attachments.html#post2545970
Both MrExcel HTML Maker and Excel jeanie are free downloads...I have used both.
I have not tried the Forum Tools Add-In or Borders-Copy-Paste.

Having said that...another approach to your particular situation might be to put 'Bank', 'Income', and 'Expense' each on a separate worksheet, then run the code I provided on each worksheet separately, and then combine (copy and paste) the results onto a 'Summary' sheet.
I have modified the earlier macro (see the red font changes) so you can run it on each worksheet individually, and then after you run the macro on each sheet, manually copy and paste the used range of each sheet onto the 'Summary' sheet.

You could use the macro recorder in the 'Developer' tab to record each step in the copy and paste process. Then we could tweak it to make it more 'dynamic' and allow for additional rows in each sheet. This is just one approach, not necessarily the best, but it may work well in this case and give you some macro experience.
I hope this is helpful.
Perpa

Sub SortMyList()
    'The following code alphbetically sorts on Column D
    'New data can be added to the bottom of the list and will be sorted  creating a'dynamic List')
    Dim LastARow As Integer      'I used column A to find the last row, change to suit and refelct change below
    [COLOR=#ff0000]Dim ws as string[/COLOR]
    LastARow = Range("A18").End(xlDown).Row  ' change to suit
    [COLOR=#ff0000]ws = Activesheet.name[/COLOR]
    With Application  'This WITH statement keeps the screen from changing until sorting is done
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    'The following sorts based on column D values in 'Ascending' order A to Z. Change 'Ascending' to 'Decending' if desired
    Range("A18:AR" & LastARow).Select    'This selects the Range to be sorted in the helper column and the original column
    ActiveWorkbook.Worksheets([COLOR=#ff0000]ws[/COLOR]).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets([COLOR=#ff0000]ws[/COLOR]).Sort.SortFields.Add Key:=Range("D18:D" & LastARow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets([COLOR=#ff0000]ws[/COLOR]).Sort      'Change this to suit if your list is not on Sheet1
        .SetRange Range("A18:AR" & LastARow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("D18").Select     'I chose a cell at the top of the sorted range to end on, change to suit
    
    With Application  'With sorting done let the screen changes be shown
        .ScreenUpdating = True
        .EnableEvents = True
    End With
 
End Sub

 

[prowebcommerce]

Wow, thank you Perpa. I really appreciate your time and patience. Great to know there are people 'out there' willing to help out.

I already have a Summary sheet, but I will bear your advice in mind (I can always call it something else of course.) For various reasons I want to have it all work on the one sheet and I know it can be done, I've seen it elsewhere but can't recall the link. I'll keep trying........

When I do get it working, I'll post it up here.

Regards,
Ron S

 

[Perpa]

Wow, thank you Perpa. I really appreciate your time and patience. Great to know there are people 'out there' willing to help out.

Regards,
Ron S

Ron S,
You are welcome...Have a look at this link, seems pretty close to what you want to do.

https://www.mrexcel.com/forum/excel-questions/1012860-sort-multiple-data-sets-same-sheet.html

Perpa

 

[MARK858]

Ron S if you just change this line in Perpa's code in post #4

LastARow = Range("A18").End(xlDown).Row

to

LastARow = Cells(Rows.Count, "A").End(xlUp).Row

What happens?

BTW, I haven't checked the rest of the code.