Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Sort Excel worksheet without sorting header

cluM09 (TechnicalUser) (OP)
16 Jun 06 10:11
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 (MIS)
16 Jun 06 10:37
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 (MIS)
16 Jun 06 10:40
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 (IS/IT--Management)
16 Jun 06 10:57
I just did my first script for Excel and had problems with sorting as well.

You could change your last line to something like:

CODE

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:

CODE

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 (TechnicalUser) (OP)
16 Jun 06 11:00
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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close