VBA Range.Sort Unusal error

I

iP_123

Board Regular
Joined
Apr 18, 2016
Messages
99
Hi

I have a sort vba that sorts specific Range in X but for some odd reason it only sorts less than half of the data (data consist of numbers ranging from $0 to ($3000)) in the specified range which most often is not more than 39 rows.
I am not quite sure what I am doing wrong in my code below. I appreciate your time. Thanks.

ActiveWorkbook.Sheets("Branch Sales Var Est").Activate


Range("C14:O53").Sort key1:=Range("O14:O53"), order1:=xlDescending, Header:=no


Range("C55:O84").Sort key1:=Range("O55:O84"), order1:=xlDescending, Header:=no


Range("C93:O103").Sort key1:=Range("O93:O103"), order1:=xlDescending, Header:=no


Range("C14:O14").Interior.ColorIndex = 15
Range("C15:O15").Interior.ColorIndex = 0
Range("C16:O16").Interior.ColorIndex = 15
Range("C17:O17").Interior.ColorIndex = 0
Range("C18:O18").Interior.ColorIndex = 15
Range("C19:O19").Interior.ColorIndex = 0
Range("C20:O20").Interior.ColorIndex = 15
Range("C21:O21").Interior.ColorIndex = 0
Range("C22:O22").Interior.ColorIndex = 15
Range("C23:O23").Interior.ColorIndex = 0
Range("C24:O24").Interior.ColorIndex = 15
Range("C25:O25").Interior.ColorIndex = 0
Range("C26:O26").Interior.ColorIndex = 15
Range("C27:O27").Interior.ColorIndex = 0
Range("C28:O28").Interior.ColorIndex = 15
Range("C29:O29").Interior.ColorIndex = 0
Range("C30:O30").Interior.ColorIndex = 15
Range("C31:O31").Interior.ColorIndex = 0
Range("C32:O32").Interior.ColorIndex = 15
Range("C33:O33").Interior.ColorIndex = 0
Range("C34:O34").Interior.ColorIndex = 15
Range("C35:O35").Interior.ColorIndex = 0
Range("C36:O36").Interior.ColorIndex = 15
Range("C37:O37").Interior.ColorIndex = 0
Range("C38:O38").Interior.ColorIndex = 15
Range("C39:O39").Interior.ColorIndex = 0
Range("C40:O40").Interior.ColorIndex = 15
Range("C41:O41").Interior.ColorIndex = 0
Range("C42:O42").Interior.ColorIndex = 15
Range("C43:O43").Interior.ColorIndex = 0
Range("C44:O44").Interior.ColorIndex = 15
Range("C45:O45").Interior.ColorIndex = 0
Range("C46:O46").Interior.ColorIndex = 15
Range("C47:O47").Interior.ColorIndex = 0
Range("C48:O48").Interior.ColorIndex = 15
Range("C49:O49").Interior.ColorIndex = 0
Range("C50:O50").Interior.ColorIndex = 15
Range("C51:O51").Interior.ColorIndex = 0
Range("C52:O52").Interior.ColorIndex = 15
Range("C53:O53").Interior.ColorIndex = 0






Range("C55:O55").Interior.ColorIndex = 0
Range("C56:O56").Interior.ColorIndex = 15
Range("C57:O57").Interior.ColorIndex = 0
Range("C58:O58").Interior.ColorIndex = 15
Range("C59:O59").Interior.ColorIndex = 0
Range("C60:O60").Interior.ColorIndex = 15
Range("C61:O61").Interior.ColorIndex = 0
Range("C62:O62").Interior.ColorIndex = 15
Range("C63:O63").Interior.ColorIndex = 0
Range("C64:O64").Interior.ColorIndex = 15
Range("C65:O65").Interior.ColorIndex = 0
Range("C66:O66").Interior.ColorIndex = 15
Range("C67:O67").Interior.ColorIndex = 0
Range("C68:O68").Interior.ColorIndex = 15
Range("C69:O69").Interior.ColorIndex = 0
Range("C70:O70").Interior.ColorIndex = 15
Range("C71:O71").Interior.ColorIndex = 0
Range("C72:O72").Interior.ColorIndex = 15
Range("C73:O73").Interior.ColorIndex = 0
Range("C74:O74").Interior.ColorIndex = 15
Range("C75:O75").Interior.ColorIndex = 0
Range("C76:O76").Interior.ColorIndex = 15
Range("C77:O77").Interior.ColorIndex = 0
Range("C78:O78").Interior.ColorIndex = 15
Range("C79:O79").Interior.ColorIndex = 0
Range("C80:O80").Interior.ColorIndex = 15
Range("C81:O81").Interior.ColorIndex = 0
Range("C82:O82").Interior.ColorIndex = 15
Range("C83:O83").Interior.ColorIndex = 0
Range("C84:O84").Interior.ColorIndex = 15






Range("C93:O93").Interior.ColorIndex = 0
Range("C94:O94").Interior.ColorIndex = 15
Range("C95:O95").Interior.ColorIndex = 0
Range("C96:O96").Interior.ColorIndex = 15
Range("C97:O97").Interior.ColorIndex = 0
Range("C98:O98").Interior.ColorIndex = 15
Range("C99:O99").Interior.ColorIndex = 0
Range("C100:O100").Interior.ColorIndex = 15
Range("C101:O101").Interior.ColorIndex = 0
Range("C102:O102").Interior.ColorIndex = 15
Range("C103:O103").Interior.ColorIndex = 0




Range("D:D").Interior.ColorIndex = 0
Range("F:F").Interior.ColorIndex = 0
Range("H:H").Interior.ColorIndex = 0
Range("J:J").Interior.ColorIndex = 0
Range("L:L").Interior.ColorIndex = 0
Range("N:N").Interior.ColorIndex = 0
Range("P:P").Interior.ColorIndex = 0


End Sub


Thanks.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Not quite sure how to attach my sample data here and my apologies I just read the rules of how to paste the vba

Rich (BB code): 
ActiveWorkbook.Sheets("Branch Sales Var Est").Activate

Range("C14:O53").Sort key1:=Range("O14:O53"), order1:=xlDescending, Header:=no

Range("C55:O84").Sort key1:=Range("O55:O84"), order1:=xlDescending, Header:=no

Range("C93:O103").Sort key1:=Range("O93:O103"), order1:=xlDescending, Header:=no

Range("C14:O14").Interior.ColorIndex = 15
Range("C15:O15").Interior.ColorIndex = 0
Range("C16:O16").Interior.ColorIndex = 15
Range("C17:O17").Interior.ColorIndex = 0
Range("C18:O18").Interior.ColorIndex = 15
Range("C19:O19").Interior.ColorIndex = 0
Range("C20:O20").Interior.ColorIndex = 15
Range("C21:O21").Interior.ColorIndex = 0
Range("C22:O22").Interior.ColorIndex = 15
Range("C23:O23").Interior.ColorIndex = 0
Range("C24:O24").Interior.ColorIndex = 15
Range("C25:O25").Interior.ColorIndex = 0
Range("C26:O26").Interior.ColorIndex = 15
Range("C27:O27").Interior.ColorIndex = 0
Range("C28:O28").Interior.ColorIndex = 15
Range("C29:O29").Interior.ColorIndex = 0
Range("C30:O30").Interior.ColorIndex = 15
Range("C31:O31").Interior.ColorIndex = 0
Range("C32:O32").Interior.ColorIndex = 15
Range("C33:O33").Interior.ColorIndex = 0
Range("C34:O34").Interior.ColorIndex = 15
Range("C35:O35").Interior.ColorIndex = 0
Range("C36:O36").Interior.ColorIndex = 15
Range("C37:O37").Interior.ColorIndex = 0
Range("C38:O38").Interior.ColorIndex = 15
Range("C39:O39").Interior.ColorIndex = 0
Range("C40:O40").Interior.ColorIndex = 15
Range("C41:O41").Interior.ColorIndex = 0
Range("C42:O42").Interior.ColorIndex = 15
Range("C43:O43").Interior.ColorIndex = 0
Range("C44:O44").Interior.ColorIndex = 15
Range("C45:O45").Interior.ColorIndex = 0
Range("C46:O46").Interior.ColorIndex = 15
Range("C47:O47").Interior.ColorIndex = 0
Range("C48:O48").Interior.ColorIndex = 15
Range("C49:O49").Interior.ColorIndex = 0
Range("C50:O50").Interior.ColorIndex = 15
Range("C51:O51").Interior.ColorIndex = 0
Range("C52:O52").Interior.ColorIndex = 15
Range("C53:O53").Interior.ColorIndex = 0

Range("C55:O55").Interior.ColorIndex = 0
Range("C56:O56").Interior.ColorIndex = 15
Range("C57:O57").Interior.ColorIndex = 0
Range("C58:O58").Interior.ColorIndex = 15
Range("C59:O59").Interior.ColorIndex = 0
Range("C60:O60").Interior.ColorIndex = 15
Range("C61:O61").Interior.ColorIndex = 0
Range("C62:O62").Interior.ColorIndex = 15
Range("C63:O63").Interior.ColorIndex = 0
Range("C64:O64").Interior.ColorIndex = 15
Range("C65:O65").Interior.ColorIndex = 0
Range("C66:O66").Interior.ColorIndex = 15
Range("C67:O67").Interior.ColorIndex = 0
Range("C68:O68").Interior.ColorIndex = 15
Range("C69:O69").Interior.ColorIndex = 0
Range("C70:O70").Interior.ColorIndex = 15
Range("C71:O71").Interior.ColorIndex = 0
Range("C72:O72").Interior.ColorIndex = 15
Range("C73:O73").Interior.ColorIndex = 0
Range("C74:O74").Interior.ColorIndex = 15
Range("C75:O75").Interior.ColorIndex = 0
Range("C76:O76").Interior.ColorIndex = 15
Range("C77:O77").Interior.ColorIndex = 0
Range("C78:O78").Interior.ColorIndex = 15
Range("C79:O79").Interior.ColorIndex = 0
Range("C80:O80").Interior.ColorIndex = 15
Range("C81:O81").Interior.ColorIndex = 0
Range("C82:O82").Interior.ColorIndex = 15
Range("C83:O83").Interior.ColorIndex = 0
Range("C84:O84").Interior.ColorIndex = 15

Range("C93:O93").Interior.ColorIndex = 0
Range("C94:O94").Interior.ColorIndex = 15
Range("C95:O95").Interior.ColorIndex = 0
Range("C96:O96").Interior.ColorIndex = 15
Range("C97:O97").Interior.ColorIndex = 0
Range("C98:O98").Interior.ColorIndex = 15
Range("C99:O99").Interior.ColorIndex = 0
Range("C100:O100").Interior.ColorIndex = 15
Range("C101:O101").Interior.ColorIndex = 0
Range("C102:O102").Interior.ColorIndex = 15
Range("C103:O103").Interior.ColorIndex = 0

Range("D:D").Interior.ColorIndex = 0
Range("F:F").Interior.ColorIndex = 0
Range("H:H").Interior.ColorIndex = 0
Range("J:J").Interior.ColorIndex = 0
Range("L:L").Interior.ColorIndex = 0
Range("N:N").Interior.ColorIndex = 0
Range("P:P").Interior.ColorIndex = 0

End Sub


And the below is the data I am trying to sort (BMW cell is C14);

BMW$3,399$2,403$3,182$2,301141.5%$996
Sedan$825$996$990$97782.8%$800
Volvo$405$0$0($0) $405
BFR$0($295)($4,779)$00.0%$295
Clk$216$0$0$0 $216
Telsa$189$0$0($0) $189
X$2,860$2,676$2,688$1,715106.9%$184
Rocket$1,788$2,311$2,933$2,61477.3%$5
Rocket$1,521$2,251$2,134$2,09667.6%$4
Rocket$0$0$0$0 $0
Rocket$0$0$0$0 $0
Rocket$418$666$838$72262.8%$0
Rocket$777$1,025$1,489$1,34375.8%$0
Rocket$502$780$874$86264.3%$0
Rocket$2,854$3,968$4,854$3,17971.9%$0
Rocket($1)$0$0($0) ($1)
Rocket($18)$0$0$0 ($18)
Rocket$825$851$833$81496.9%($26)
Rocket$640$688$796$78193.1%($47)
Rocket$1,258$1,338$1,500$1,52594.1%($80)
Rocket$1,742$1,908$1,445$1,21591.3%($165)
Rocket$1,026$1,200$1,167$1,14785.5%($175)
Rocket$1,119$1,295$1,471$1,48686.4%($176)
Rocket$1,544$2,150$2,893$2,58771.8%($606)
Rocket$916$1,093$1,667$77583.8%($177)
Rocket$1,512$1,700$2,000$2,42689.0%($187)
Rocket$1,231$1,445$1,375$1,42785.2%($213)
Rocket$449$670$889$87867.0%($221)
Rocket$1,387$1,645$1,555$1,53884.3%($258)
Rocket$1,081$1,423$1,019$1,02976.0%($342)
Rocket$2,459$3,306$4,096$4,07874.4%($847)
Rocket$641$984$1,000$95565.2%($343)
Rocket$773$1,186$1,143$90165.2%($412)
Rocket$552$997$1,114$1,57255.3%($446)
Rocket$1,507$1,971$2,010$1,95576.4%($464)
Rocket$733$1,362$1,146$1,30553.8%($629)
Rocket$1,190$2,285$2,667$2,07352.1%($1,095)
Rocket$2,183$3,706$3,636$2,15158.9%($1,523)
Rocket$40,503$49,984$50,626$48,42881.0%($9,481)
Rocket$0$0$0$0 $0
Rocket$2,535$3,230$3,418$3,46378.5%($695)
Rocket$0$0$0$0 $0
Rocket$1,783$1,290$1,202$1,112138.2%$493
Rocket$0$0$0$0 $0
Rocket$0$0$0$0 $0
Rocket$0$0$0$0 $0
Rocket$631$940$940$89267.1%($309)
Rocket$708$1,195$1,385$1,36559.2%($487)
Rocket$0$0$0$0 $0
Rocket$415$480$470$43286.5%($65)
Rocket$1,944$2,040$2,019$1,86795.3%($96)
Rocket$540$652$645$65182.8%($112)
Rocket$1,059$1,353$1,400$1,34578.3%($294)
Rocket$66$0$0$0 $66
Rocket$4,194$5,864$6,438$6,69971.5%($1,670)
Rocket$0$0$0$0 $0
Rocket($1)$0$0$0 ($1)
Rocket$898$1,042$1,042$96586.2%($144)
Rocket$806$990$1,130$1,12781.4%($184)
Rocket$630$830$830$85275.9%($200)
Rocket$364$584$584$58462.3%($220)
Rocket$569$800$769$92871.1%($231)
Rocket$636$875$874$91572.6%($239)
Rocket$2,045$2,620$2,624$2,74478.1%($575)
Rocket$39$830$829$8144.7%($791)
Rocket$553$4,145$4,234$4,10413.4%($3,592)
Rocket$38$1,109$1,109$1,1553.4%($1,071)
Rocket$690$2,678$2,854$2,74725.8%($1,988)
Rocket$275$3,941$4,211$4,1637.0%($3,666)
Rocket$21,416$37,439$38,991$38,92457.2%($16,024)
Rocket$0$0$0$0 $0
Rocket$0$0$0$0 $0
Rocket$0$0$0$0 $0
Rocket$62,144$88,873$91,174$88,84869.9%($26,730)
Rocket$0$0$0$00.0%$0
Rocket
Rocket
Rocket$271$196$196$137138.7%$76
Rocket$0$0$0$0 $0
Rocket$0$0$0$0 $0
Rocket$0$0$0$0 $0
Rocket$0$0$0$0 $0
Rocket$0$0$0$0 $0
Rocket$0$0$0$0 $0
Rocket($571)$108$240$0-530.3%($679)
Rocket$6,063$7,324$7,324$7,98082.8%($1,262)
Rocket$4,806$7,328$7,335$8,79465.6%($2,522)
Rocket$33,985$40,240$39,283$41,14584.5%($6,255)

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
I am not sure why your sorts are not working.

But theres no real need to set your cell color in the macro.

I would select the range C14:P103 then go to Conditional Formatting. Under the Classic menu, Format using a formula and type in the following
=AND(IsEven(Row(C14)), IsOdd(Column(C14)))
then choose the grey color you like for the cell background.

After doing this you can erase that long list of interior.colorindex commands in your code. speeding it up significantly.
 
Upvote 0
What is Column O formatted as?

When I put your data into a worksheet and tried to sort column O It was sorting it alphabetically.... therefore,
$800
$5
$405
$4
etc.....

Not numerically.
 
Upvote 0
Thank you tygrrboi. Column O is fromatted as Currency. Perharps I need to change it to Numbers if that will solve the issue.
 
Upvote 0
I changed the Format in Column O to Numbers and it still doesn't sort the ranges correctly.
 
Upvote 0
Where do you have the code? In a standard Module or a particular worksheet's Module? If worksheet, which worksheet?

Possibly not too important with this particular code, but I would recommend always having Option Explicit at the top of your code module. You can have that appear automatically and it would prevent what I presume is an oversight with these lines where I presume you really meant Header:=xlNo
Code: 
Range("C14:O53").Sort key1:=Range("O14:O53"), order1:=xlDescending, Header:=no

Range("C55:O84").Sort key1:=Range("O55:O84"), order1:=xlDescending, Header:=no

Range("C93:O103").Sort key1:=Range("O93:O103"), order1:=xlDescending, Header:=no

BTW, the code sorts fine for me if code is placed in a standard Module.

Can you explain, or show, in what way the sort fails for you?
 
Upvote 0
It's an xlsm workbook and the workheet name is "Branch Sales Var Est". I inserted a module when I was in the vba and edited this macro. That is all I did. When I run the macro below results is what I get, it's wiered cos it is still not sorting coulnm O in decending order as seen below'

BMW3,3992,4033,1822,3011996
Sedan40500(0)405
Volvo0(295)(4,779)00295
BFR216000216
Clk18900(0)189
Telsa2,8602,6762,6881,7151184
X00000
Rocket00000
Rocket(1)00(0)(1)
Rocket(18)000(18)
Rocket8258518338141(26)
Rocket6406887967811(47)
Rocket1,2581,3381,5001,5251(80)
Rocket1,7421,9081,4451,2151(165)
Rocket8259969909771(171)
Rocket1,0261,2001,1671,1471(175)
Rocket1,1191,2951,4711,4861(176)
Rocket1,5442,1502,8932,5871(606)
Rocket00000
Rocket9161,0931,6677751(177)
Rocket1,5121,7002,0002,4261(187)
Rocket1,2311,4451,3751,4271(213)
Rocket4496708898781(221)
Rocket4186668387221(248)
Rocket7771,0251,4891,3431(248)
Rocket1,3871,6451,5551,5381(258)
Rocket5027808748621(279)
Rocket1,0811,4231,0191,0291(342)
Rocket2,4593,3064,0964,0781(847)
Rocket00000
Rocket6419841,0009551(343)
Rocket7731,1861,1439011(412)
Rocket5529971,1141,5721(446)
Rocket1,5071,9712,0101,9551(464)
Rocket1,7882,3112,9332,6141(524)
Rocket7331,3621,1461,3051(629)
Rocket1,5212,2512,1342,0961(730)
Rocket1,1902,2852,6672,0731(1,095)
Rocket2,8543,9684,8543,1791(1,115)
Rocket2,1833,7063,6362,1511(1,523)
Rocket40,50349,98450,62648,4281(9,481)
Rocket00000
Rocket2,5353,2303,4183,4631(695)
Rocket00000
Rocket1,7831,2901,2021,1121493
Rocket00000
Rocket00000
Rocket00000
Rocket6319409408921(309)
Rocket7081,1951,3851,3651(487)
Rocket00000
Rocket4154804704321(65)
Rocket1,9442,0402,0191,8671(96)
Rocket5406526456511(112)
Rocket1,0591,3531,4001,3451(294)
Rocket6600066
Rocket4,1945,8646,4386,6991(1,670)
Rocket00000
Rocket(1)000(1)
Rocket8981,0421,0429651(144)
Rocket8069901,1301,1271(184)
Rocket6308308308521(200)
Rocket3645845845841(220)
Rocket5698007699281(231)
Rocket6368758749151(239)
Rocket2,0452,6202,6242,7441(575)
Rocket398308298140(791)
Rocket5534,1454,2344,1040(3,592)
Rocket381,1091,1091,1550(1,071)
Rocket6902,6782,8542,7470(1,988)
Rocket2753,9414,2114,1630(3,666)
Rocket21,41637,43938,99138,9241######
Rocket00000
Rocket00000
Rocket00000
Rocket62,14488,87391,17488,8481######
Rocket000000
Rocket
Rocket
Rocket271196196137176
Rocket00000
Rocket00000
Rocket00000
Rocket00000
Rocket00000
Rocket00000
Rocket(571)1082400(5)(679)
Rocket6,0637,3247,3247,9801(1,262)

<tbody>
</tbody>

Perharps I should delete the workbook and resart from the begining cos I am at a loss.

Thanks Peter.

All values are Formatted as Numbers and Column O has the values 996, 405, 295 etcetera.
 
Last edited:
Upvote 0
I started from scratch using the same logic but different worksheet and it works fine now.


Thank you all for your invaluable assistance and advise.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Speeding up a VBA Macro & Code Cleanup
Replies
3
Views
338
Nordicrx8
N
F
  • Solved
A simple project update VBA that is not match the projects
Replies
9
Views
223
NoSparks
N
O
  • Question
Excel insists on trying to repair a super basic workbook and worksheet. What can I try or look for?
Replies
3
Views
172
Fluff
Fluff
G
  • Solved
VBA CODE NOT WORKING WITH OTHER WORKBOOK
Replies
5
Views
414
Alex Blakenburg
A
A
  • Question
Automating VBA Border Concise Formula
Replies
4
Views
271
AJLS
A

Forum statistics

Threads
1,214,936
Messages
6,122,340
Members
449,079
Latest member
rocketslinger

Share this page

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