Trouble with VBA sort. Too many columns?

[sir drinks alot]

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:

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.

 

[erik.van.geit]

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

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

With Sheets(1)
.Range("A1") = 123
End With

applied to your 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]

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
	A	B	C	D	E	F	G
1		Master Schedule				Current Job Status
2
3	Facility	Job City	Intake Date	Due Date	Status	Request Date	Active
4	1	Powell, TN	9/13/2006	10/5/2006	Prelim	9/13/2006	TRUE
5	1	Morristown, TN	9/13/2006	10/6/2006	Prelim	9/13/2006	TRUE
6	1	Knoxville, TN	9/13/2006	10/9/2006	Prelim	9/13/2006	TRUE
7	2	Dinuba, CA	9/12/2006	9/14/2006	Prelim	9/12/2006	TRUE
8	2	Naples, FL	9/13/2006	9/27/2006	Finals	9/13/2006	TRUE
9	1	Cape Coral, FL (Chiquita)	9/12/2006	9/26/2006	Finals	9/12/2006	TRUE
10	2	Bonita Springs, FL	9/13/2006	9/27/2006	Finals	9/13/2006	TRUE
11	1	Hicksville, NY (Bethage)	9/12/2006	9/26/2006	Finals	9/12/2006	TRUE
12	1	Lewistown, PA	9/13/2006	9/27/2006	Prelim	9/13/2006	TRUE
13	2	Pocahontas, AR	9/13/2006	9/20/2006	C.O. 1	9/13/2006	TRUE
14	1	Rayville, LA	9/12/2006	9/14/2006	C.O. 2	9/12/2006	TRUE
15	1	San Dimas, CA	9/12/2006	9/19/2006	Prelim	9/12/2006	TRUE
16	1	Hemet, CA	9/12/2006	9/20/2006	Prelim	9/12/2006	TRUE
17	1	Colton, CA	9/12/2006	9/21/2006	Prelim	9/12/2006	TRUE
18	1	Rancho Cucamongo, CA	9/12/2006	9/22/2006	Prelim	9/12/2006	TRUE
19	2	Cortland, NY	9/12/2006	9/22/2006	C.O. 1	9/12/2006	TRUE
20	1	Ponoma Indian Hill, CA	9/12/2006	9/25/2006	Prelim	9/12/2006	TRUE
21	1	Fontana, CA	9/12/2006	9/26/2006	Prelim	9/12/2006	TRUE
22	1	Pomona, CA	9/12/2006	9/18/2006	Prelim	9/12/2006	FALSE
23	1	Fredericksburg, VA	9/13/2006	9/21/2006	Finals	9/13/2006	TRUE
main

the sheet is called main, here is the code i modified:

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.

 

[sir drinks alot]

BUMP

 

[erik.van.geit]

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]

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:

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

 

[sir drinks alot]

i am sorting 8 other sheets with this code, is that a problem?

 

[erik.van.geit]

i am sorting 8 other sheets with this code, is that a problem?

depends on what you mean
you can easily make a loop, did you ?