Sort Excel worksheet without sorting header

[cluM09]

I have a script to generate data and write to the Excel worksheet. I want to sort the data in the worksheet but not the header.

Is there anyway to sort a particular field without sorting the header? I have an example code that is listed below, but it will sort the header also even if I indicated "A2" as the starting point:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Cells(1, 1).Value = "Number"
objExcel.Cells(2, 1).Value = "9"
objExcel.Cells(3, 1).Value = "2"
objExcel.Cells(4, 1).Value = "60"
objExcel.Cells(5, 1).Value = "0"
objExcel.Cells(6, 1).Value = "8"
objExcel.Cells(7, 1).Value = "2"
objExcel.Cells(1, 2).Value = "Letter"
objExcel.Cells(2, 2).Value = "B"
objExcel.Cells(3, 2).Value = "C"
objExcel.Cells(4, 2).Value = "P"
objExcel.Cells(5, 2).Value = "K"
objExcel.Cells(6, 2).Value = "J"
objExcel.Cells(7, 2).Value = "O"

Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range("A2")
objRange.Sort(objRange2)

Thanks!

 

[barny2006]

if you highlight the rows in a cloumn and sort them, it works ok. you may want to use macro recorder to see how excel does this. and copy the code.

 

[barny2006]

here's the code:

Range("A2:A6").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

 

[PPettit]

I just did my first script for Excel and had problems with sorting as well.

You could change your last line to something like:

objRange.Sort objRange2,1,,,1,,1,1,1,False,1

Maybe this will help you. This is a part of my script that includes notes I made about each portion of the sort line:

Dim objRange, ColumnA, ColumnB, ColumnC
    Dim Key1, Order1, Key2, sortType, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation

    Set objRange = objSheet.UsedRange
    Set ColumnA = objExcel.Range("A2")
    Set ColumnB = objExcel.Range("B2")
    Set ColumnC = objExcel.Range("C2") 
    
    'Key1 = defined range
    Order1 = 1 'sort order - (default) 1 = ascending, (?)2 = descending
    'Key2 = defined range
    'Type = (?) 'ignored for the Range object
    Order2 = 1 'sort order - (default) 1 = ascending, (?)2 = descending
    'Key3 = defined range
    Order3 = 1 'sort order - (default) 1 = ascending, (?)2 = descending
    Header = 1 'use first row as column headings - (default) 1 = Yes, (?)2 = No
    OrderCustom = 1 'index of custom sort order from Sort Options dialog box - (default) 1 = Normal
    MatchCase = False 'True = case sensitive, (default) False = ignore case
    Orientation = 1 '(default) 1 = top to bottom, (?)2 = left to right
    
    Order = MsgBox("Sort by description?.  No = Sort by code", 3, "Sort Order")
    Select Case Order
      Case "6"
        objRange.Sort ColumnB,Order1,ColumnA,,Order2,ColumnC,Order3,Header,OrderCustom,MatchCase,Orientation
      Case "7"
        objRange.Sort ColumnA,Order1,ColumnB,,Order2,ColumnC,Order3,Header,OrderCustom,MatchCase,Orientation

I found the info by looking at the Object Browser within Excel's built in Visual Basic Editor (Tools->Macro->Visual Basic Editor->View->Object Browser).

 

[cluM09]

I finally figured it out. Thanks anyway!

Below is code:

Const xlAscending = 1
Const xlDescending = 2
Const xlYes = 1

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Cells(1, 1).Value = "Number"
objExcel.Cells(2, 1).Value = "9"
objExcel.Cells(3, 1).Value = "2"
objExcel.Cells(4, 1).Value = "60"
objExcel.Cells(5, 1).Value = "0"
objExcel.Cells(6, 1).Value = "8"
objExcel.Cells(7, 1).Value = "2"
objExcel.Cells(1, 2).Value = "Letter"
objExcel.Cells(2, 2).Value = "B"
objExcel.Cells(3, 2).Value = "C"
objExcel.Cells(4, 2).Value = "P"
objExcel.Cells(5, 2).Value = "K"
objExcel.Cells(6, 2).Value = "J"
objExcel.Cells(7, 2).Value = "O"

Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range("A1")
objRange.Sort objRange2, xlAscending, , , , , , xlYes