VBA code for Excel: Sorting by multiple columns at once

[DL21]

Hi,

I am having trouble finding examples of code to where I can sort an excel spreadshet by multiple columns at one time.

The code I'm using for sorting one at a time is as follows:

excelApp.Worksheets("Temp").UsedRange.Sort excelApp.Range("A1"),xlAscending

What do I have to add to this to sort by column b as well?

An example of what I'm trying to do...Say there are 50 people divided in to 5 teams. Column A is their team Number and Column B is their last name. I want to sort them first by their Team Number then Second by their last name. This is really easy to do in excel using their sort tool, but I am generating a ton of reports through this code and it would be a pain to have to do this manually for each report.

Thanks for any help you can provide in advance.

DL

[RobDog888]

Welcome to the Forums.

Here is about all that you need.

Workbooks(1).Sheets(1).Range("A:A").Sort Key1:=Workbooks(1).Sheets(1).Range("A:A"), Order1:=xlAscending, Key2:=Workbooks(1).Sheets(1).Range("B:B"), Order2:=xlAscending, Orientation:=xlSortRows

[DL21]

Thanks for the help. I'm having a little trouble getting it to compile. What goes where the (1) is? My excel document doesn't have a name yet and the worksheet I'm using is "temp"

[DL21]

Guess I should have posted this under the vb script board? Is the code much different for vbscript?

[RobDog888]

It is just the positioning of the collection of workbooks and sheets.
Even if its not saved you can use the excelApp.Worksheets("Temp"). Where Temp is the name of the workbook. "Book1" is the default until its saved.

[RobDog888]

Well this sounds like your using VB6 to automate Excel, so this is an ok spot for your thread.

VBA is the "script" language that Excel and other Office apps use. So if your coding directly behind Excel then the thread would be
better in the VBA forum.

[DL21]

Thanks again, So just to make sure I understood you correctly I can do this:

excelApp.Worksheets("Temp").Range.Sort Key1:=excelApp.Worksheets("Temp").Range("A:A"), Order1:=xlAscending, key2:=excelApp.Worksheets("Temp").Range("B:B"), Order2:=xlAscending, Orientation:=xlSortRows


-------------
If it is the case that I understood you correctly, I am getting and "Expected Statement" error.

[RobDog888]

Yes, as long as "temp" is your workbook name.

[DL21]

Ok, I got it to compile by taking out the := and just using = . When it runs I get a type mismatch error, any ideas?

[RobDog888]

Are you early or late binding? Can you post your code?

[john24]

alternative: record a macro doing exactly what you want and then import that code

[RobDog888]

Macros are good to determine what methods and function Excel uses for a task, but they usually contain allot of code padding that is not
needed and will slow down your app.

I'd take the one line of code verses the several a macro will generate, but in this situation its the same code.

[DL21]

Not sure what you mean by early or late binding. Here is the code that I am using to do the sort:

excelApp.Workbooks(1).Sheets(1).UsedRange.Sort Key1=excelApp.Workbooks(1).Sheets(1).Range("C:C"), Order1=xlAscending, key2=excelApp.Workbooks(1).Sheets(1).Range("I:I"), Order2=xlAscending, Orientation=xlSortRows

[DL21]

I took code straight from an excel sheet and I still get the type mismatch error...

[RobDog888]

If you didnt add a reference to MS Excel xx.0 Object Library and you create your excel app object like "CreateObject then thats
late binding. If you added the ref. and you use Set excelApp = New Excel.Application then thats early binding.

the reason is that if you late bind then you either need to declare your consts or use the numeric values only.

[DL21]

I used the following code to declare my object, which I believe you noted as late binding:

Dim excelApp 'MS Excel Application Object
'start the word application object
Set excelApp = CreateObject("Excel.Application")
excelApp.Visible = false

How would I change the following statement to account for the late binding?
excelApp.Worksheets("Temp").UsedRange.Sort Key1:=excelApp.Worksheets("Temp").Range("A:A"), Order1:=xlAscending, key2:=excelApp.Worksheets("Temp").Range("B:B"), Order2:=xlAscending, Orientation:=xlSortRows


----------------------------------
Side note:

I was able to get in contact with the person that wrote this code, and he gave me the following code and said it should work:

excelApp.WorkSheets("Temp").UsedRange.Sort excelApp.Range("A1"),xlAscending, excelApp.Range("B1"),xlAscending

I get the following error:

Error Number: 1004
Error Source: Microsoft Office Excel
Error Description: Reference is not valid

Is there an easy fix for this? A solution for either should work.

Thanks for any help in advance!

[DL21]

Does it have to be the 1st 2 columns in the spreadsheet?

The data that I need to have sorted is in columns C and D. Could that be causing the problem?

[RobDog888]

The code they gave you will not work since the range is only for the first row only - A1.

You can sort on ANY columns you need.

To make it for Late binding you need to take out the labels and consts. and keep the positioning of the parameters according to the function.
For Excel 2003:

.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod
 
excelApp.Worksheets("Temp").UsedRange.Sort excelApp.Worksheets("Temp").Range("C:C"), 1, excelApp.Worksheets("Temp").Range("D:D"), , 1, , , , , , 2

[DL21]

Thanks Rob, that did the trick! I really appreciate it.

[RobDog888]

Glad to help. I should have had this solved earlier but I think I am having an off day today. Plus, I'm bored.