Trouble with VBA sort. Too many columns?

sir drinks alot

New Member
Joined
Aug 1, 2006
Messages
37
I am trying to sort multiple rows of information with about 182 columns filled, using VBA Sort.
I am sorting first by a column with true, false values in it, then by a date column. for some reason it does not work correctly. here is the code:
Code:
Dim LastR As Range
With ActiveWorkbook.Sheets("main")
Range("4:1000").Select

Selection.SORT Key1:=Range("W4"), Order1:=xlDescending, Key2:=Range("H4") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
End With

If i use the same code on only two columns next to each other, true/fasle & dates, it works fine. Does it have something to do with having so many columns of info?

Thanks.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

after a few test, I cannot find the problem, unless ...

one REMARK
your With ... End With makes no sense as long as the ranges in between don't refer to it

example
Code:
With Sheets(1)
Range("A1") = 123
End With
123 will be filled in in the ACTIVE sheet
you need a DOT to make the WITH work
Code:
With Sheets(1)
.Range("A1") = 123
End With

applied to your code
Code:
With ActiveWorkbook.Sheets("main")
.Range("4:1000").Sort Key1:=.Range("W4"), Order1:=xlDescending, Key2:=.Range("H4") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
End With

so the only reason I can imagine for this code not working, would be that "main" is not the activesheet when the code is running

kind regards,
Erik
 

sir drinks alot

New Member
Joined
Aug 1, 2006
Messages
37
Erik,
thanks for the help.
here's a dumbed down version of the sheet, i had to delete some columns with sensitive info.
sortHTML.xls
ABCDEFG
1Master ScheduleCurrent Job Status
2
3FacilityJob CityIntake DateDue DateStatusRequest DateActive
41Powell, TN9/13/200610/5/2006Prelim9/13/2006TRUE
51Morristown, TN9/13/200610/6/2006Prelim9/13/2006TRUE
61Knoxville, TN9/13/200610/9/2006Prelim9/13/2006TRUE
72Dinuba, CA9/12/20069/14/2006Prelim9/12/2006TRUE
82Naples, FL9/13/20069/27/2006Finals9/13/2006TRUE
91Cape Coral, FL (Chiquita)9/12/20069/26/2006Finals9/12/2006TRUE
102Bonita Springs, FL9/13/20069/27/2006Finals9/13/2006TRUE
111Hicksville, NY (Bethage)9/12/20069/26/2006Finals9/12/2006TRUE
121Lewistown, PA9/13/20069/27/2006Prelim9/13/2006TRUE
132Pocahontas, AR9/13/20069/20/2006C.O. 19/13/2006TRUE
141Rayville, LA9/12/20069/14/2006C.O. 29/12/2006TRUE
151San Dimas, CA9/12/20069/19/2006Prelim9/12/2006TRUE
161Hemet, CA9/12/20069/20/2006Prelim9/12/2006TRUE
171Colton, CA9/12/20069/21/2006Prelim9/12/2006TRUE
181Rancho Cucamongo, CA9/12/20069/22/2006Prelim9/12/2006TRUE
192Cortland, NY9/12/20069/22/2006C.O. 19/12/2006TRUE
201Ponoma Indian Hill, CA9/12/20069/25/2006Prelim9/12/2006TRUE
211Fontana, CA9/12/20069/26/2006Prelim9/12/2006TRUE
221Pomona, CA9/12/20069/18/2006Prelim9/12/2006FALSE
231Fredericksburg, VA9/13/20069/21/2006Finals9/13/2006TRUE
main


the sheet is called main, here is the code i modified:
Code:
With ActiveWorkbook.Sheets("main")
.Range("4:1000").SORT Key1:=Range("G4"), Order1:=xlDescending, Key2:=Range("D4") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
End With

i have columns up too FZ, not sure why this wont work.
the sheet i posted is after the sort, you can see a false before a true, and the dates not in order.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

what do you get when putting this formula in another column row 4
=LEN(G4)
then copy down

reason for my question: there might be a space before the TRUE
that's the only way I could reproduce the problem

you didn't tell what's coming after this
do you have more TRUE and FALSE mixed up further on ?

perhaps:
do you have any more code which could influence the process ?
is there something in the workbook or sheetmodule ?
 

pfarmer

Well-known Member
Joined
Jul 6, 2005
Messages
550
Erik,
thanks for the help.
here's a dumbed down version of the sheet, i had to delete some columns with sensitive info.

the sheet is called main, here is the code i modified:
Code:
With ActiveWorkbook.Sheets("main")
.Range("4:1000").SORT Key1:=Range("G4"), Order1:=xlDescending, Key2:=Range("D4") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
End With

i have columns up too FZ, not sure why this wont work.
the sheet i posted is after the sort, you can see a false before a true, and the dates not in order.

Erik mentioned a possible space in front of a true/false. I had a similar problem with True/False values which were imported from a text file. Not sure why Excel would do so, but these would come in with spaces at times. In my case I used the Trim function to clean them up on import.

Another item I have run into recently is xlGuess. This did odd things during sorts on some of my sheets, on others it seemed to have no effect. It seems that if you select no header Excel puts in xlGuess. As an experiment I tried xlNo and those sheets seem to be sorting properly now.

On with another item, I don't know if Erik was thinking about this or not when he asked about 'other code'. Do you have a worksheet change event associated with this sheet that could affect the sort. If so you can turn events off during the sort. I ran into this and it was hard to track down, only causing problems on some machines. Mine would do the sort properly, others would not. To me it appeared that the speed of the machine enfluenced how much of the sort could take place prior to the worksheet change event firing off. On these machines the result was offset lines, it was like the first 10 or so colums would be like the originals only sorted and the columns after these belonged to another row, often offset by one.

Perry
 

Forum statistics

Threads
1,141,588
Messages
5,707,264
Members
421,498
Latest member
matinebi

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
Top