Quick test professional

QTP Tips QTP codes QTP Faqs and more

Excel Sorting(Ascending , Descending) By Rows and Columns

Excel Sorting By Row:

Const xlAscending = 1
Const xlNo = 2
Const xlSortRows = 2

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open(“C:Documents and Settingsmohan.kakarlaDesktopDocs1.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)

Set objRange = objExcel.ActiveCell.EntireRow
objRange.Sort objRange, xlAscending, , , , , , xlNo, , , xlSortRows
set objExcel=nothing

Excel Sorting By Colum :

Const xlAscending = 1’represents the sorting type 1 for Ascending 2 for Desc
Const xlYes = 1

Set objExcel = CreateObject(“Excel.Application”)’Create the excel object
objExcel.Visible = True’Make excel visible
Set objWorkbook = _
objExcel.Workbooks.Open(“C:\Documents and Settings\mohan.kakarla\Desktop\Docs1.xls”)’Open the


Set objWorksheet = objWorkbook.Worksheets(1)’select the sheet based on the index .. 1,2 ,3 …
Set objRange = objWorksheet.UsedRange’which select the range of the cells has some data other than blank
Set objRange2 = objExcel.Range(“A1”)’ select the column to sort

objRange.Sort objRange2, xlAscending, , , , , , xlYes
set objExcel=nothing

Reference: MSDN


February 26, 2008 - Posted by | Excel Automation | , , , , , , , , , , , ,


  1. thanks for the comments

    Set objWorkbook = _
    objExcel.Workbooks.Open(”C:\Documents and Settings\mohan.kakarla\Desktop\Docs1.xls”)’Open thedocument

    is this one line??
    why are they in two diffrent lines

    Set objWorkbook =objExcel.Workbooks.Open(”C:\Documents and Settings\mohan.kakarla\Desktop\Docs1.xls”)’Open thedocument


    Comment by Sharma | April 3, 2008 | Reply

  2. Mohan,

    Would you have an Excel template on how to use data table parameters to import into QTP?

    Comment by sqatester_elite | May 23, 2008 | Reply

  3. Hi Mohan,
    Looking too smart in the photo.

    Comment by Leena | July 17, 2008 | Reply

  4. What alterations have to be made to sort a excel table by col. with more than one reiteration (?) of sorting?

    Comment by John P | March 18, 2009 | Reply

  5. If I try sorting on more than 2 fields it gives the following error: Reference not valid.
    Set objRange = objWorksheet.UsedRange
    Set sortKey1 = objExcel.Range(“C1”)
    Set sortKey2 = objExcel.Range(“A1”)
    objRange.Sort sortKey1, xlAscending, sortKey2, xlAscending , , , , xlTopToBottom

    Does any one have answer to this?

    Comment by vivek | May 20, 2009 | Reply

    • Dear vivek,
      change the statement to

      objRange.Sort sortKey1, xlAscending, sortKey2, , xlAscending, , , xlTopToBottom

      Comment by Ronan | October 15, 2009 | Reply

      • I want to custom sort for four columns , For applying sorting for 3 columns above code works fine , but how should i write code i need to apply custom sort on four or 5 columns

        Comment by kiran | November 6, 2013

      • For applying sorting for 3 columns above code works fine ,I want to custom sort on four or more columns .How should i write code if i need to apply custom sort on four or 5 columns

        Comment by kiran | November 6, 2013

  6. Hi mohan,
    Could you please provide the working code for keyword framework using script.

    Appropriated you help in advance.


    Comment by nitin | January 18, 2011 | Reply

  7. I want to log out fron my yahoo mail box

    Comment by gaby | September 26, 2011 | Reply

  8. Hi, i have the column index. I want to sort the excel using that column.
    What should i do??
    I am using following code to get the column index of column ‘ChildProperty’:-

    For i = 1 to m_ColCount
    sChildPropertyAgg = objTempWorksheet.Cells( 1,i).Value
    If Ucase(Trim(sChildPropertyAgg)) =Ucase(Trim(m_ObjPropertyColumnName)) Then
    m_ChildPropertyColumn = i
    Exit For
    End if

    I want to sort the excel on column ‘ChildProperty’.
    Pls help me out.

    Comment by Anand | December 19, 2011 | Reply

  9. Hi,
    I am filtering the data in excel.
    Initially i have 500 rows.
    After filtering i am getting 50 rows.
    I want to use ‘FOR Loop’ through these filtered 50 rows.
    How to point to only filtered rows??
    I am using below code:-

    objTempWorksheet.Cells(1, m_ChildPropertyColumn).Autofilter m_ChildPropertyColumn, m_ObjProperty
    m_RowCount = objTempWorksheet.usedrange.rows.count

    i am getting m_RowCount = 500 after filter also.

    Comment by Anand | December 19, 2011 | Reply

  10. hi mohan ,

    I am shivakumar working as testing enginner ….i m learning the QTP …i wan to data driv the scripts….so i jus need a script for importing the test data from excel sheet and exporting the results of QTP to excel sheet can you pls send tht script to my mail id mkshiva89@gmail.com

    Comment by shivakumar | February 1, 2012 | Reply

  11. hi mohan,

    i need vb script for the above mentioned stuff

    Comment by shivakumar | February 1, 2012 | Reply

  12. Hi Mohan,
    I am looking for a code for remote QC access trhough QTP vb script, can you please help me out.

    Comment by ravindar | July 3, 2012 | Reply

  13. What about Descending order? Please send me the code for Desc…

    Comment by Jp | September 26, 2012 | Reply

  14. hi mohan If I try sorting on more than 2 fields it gives the following error:The Test run cannot continue due to the syntax error….i tried as per ur modificaton like sortkey1..but iam facing this problem continuously…can u pls solve my prob

    Comment by akk | October 28, 2012 | Reply

  15. I drop a leave a response when I appreciate a post on a blog or I have something to contribute to the conversation.
    Usually it’s caused by the sincerness communicated in the post I read. And on this post Excel Sorting(Ascending , Descending) By Rows and Columns Quick test professional. I was moved enough to post a leave a responsea response 😉 I do have a couple of questions for you if it’s okay.
    Could it be simply me or does it give the impression like
    some of the remarks come across like they are written by
    brain dead folks? 😛 And, if you are posting at other online sites, I’d like to keep up with you. Would you make a list all of all your shared pages like your linkedin profile, Facebook page or twitter feed?

    Comment by www.youtube.com | February 7, 2013 | Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: