VBA for sorting while ignoring negatives.

[Dingle]

[TABLE="width: 500"]
<tbody>[TR]
[TD]F

[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]-8
[/TD]
[TD]123
[/TD]
[TD]-8
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]-8
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6
[/TD]
[TD]-8
[/TD]
[TD]-5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-4
[/TD]
[TD]5
[/TD]
[TD]132
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-2
[/TD]
[TD]-3
[/TD]
[TD]131
[/TD]
[TD]5

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-8
[/TD]
[TD][/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]999
[/TD]
[/TR]
</tbody>[/TABLE]
Dear all,


I’ve been struggling with the ending of my ratherlarge macro converting information out of our Financial bookkeeping system.

Little bit of context:
This is concerns outstanding invoices of clients,divided by client in rows and the amounts outstanding split in columns in a fewcategories. Categories being divided in:

• Not due
• 0-10 days due
• 11-20 days due
• 21-60 days due
• 60> days due

There are numbers everywhere, mostly being positivenumbers.
The problem I face is that I try to have a Sortingcode in the macro, sorting the 5 columns, high to low(descending) but ignoring negativenumbers. It should look like this:


(Sorry, example of the table on top of the post, cant seem to get it here)



The sorting of the 0 or negatives is not important.

What have I tried?

1) Use ‘record macro’ and adjusting it with variables:

 ActiveWorkbook.Worksheets("Blad1").Sort.SortFields.AddKey:=Range("F8", Range("F8").End(xlDown)), _

SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

Hoped to be able to adjust the xlSortOnValues line orat alter the code.

2) I found this post on this forum; https://www.mrexcel.com/forum/excel-questions/1057699-vba-sort-ascending-descending-order-based-if-value-positive-negative.html
I have been trying to alter the code posted at may 31^st,2018, 03:47PM, but unsuccessfully.

Now I have to admit, I’ve been using macro’s a lot byjust recording and altering the code to work on different variables. Myknowledge on VBA seems to be inferior.
Can any one perhaps push me in the right direction? Ifear to be needing to use Dimensions, which aren’t 100% clear for me yet.

 

[Yongle]

Re: VBA Marco for sorting while ignoring negatives.

Welcome to the forum

1 are your headers in row 1 starting in A1?
2 does your data start in row 2?
3 which columns require sorting? (F G H I J ?)
4 is each column sorted separately and results evaluated or should columns be sorted together in a specific sequence of priority?
5 what is the sort order for each column (are all smallest to largest? )

 

[Dingle]

Re: VBA Marco for sorting while ignoring negatives.

Welcome to the forum

1 are your headers in row 1 starting in A1?
2 does your data start in row 2?
3 which columns require sorting? (F G H I J ?)
4 is each column sorted separately and results evaluated or should columns be sorted together in a specific sequence of priority?
5 what is the sort order for each column (are all smallest to largest? )

Thank you for your reply.
I’d hoped to have everything typed out as, so apologies for the missing info.


1) The information is set from A7 to O7 with the 7^throw being headers. Data starting from Row 8. There is client info column A-Eand K-O but always only having this info starting in row 8. Columns F,G,H,I,Jcontains the criteria for the sorting, the entire row should shift. But basedon F to J

2) data starts row 8
3) The entire row (A-O) but based on F,G,H,I,J
4) These columns aren’t merged, not really sure whatyou mean with this question, but I hope to have answered this at nr 1.
5) Descending, Highest to lowest (excl. 0 /negatives).