INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • 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!

*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

Jobs from Indeed

Sort Excel worksheet without sorting header

Sort Excel worksheet without sorting header

(OP)
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!

RE: Sort Excel worksheet without sorting header

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.

RE: Sort Excel worksheet without sorting header

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

RE: Sort Excel worksheet without sorting header

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).

RE: Sort Excel worksheet without sorting header

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

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!

Resources

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