Announcement

Collapse
No announcement yet.

Sort rows alphabetically by whats in colum A

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Sort rows alphabetically by whats in colum A

    Does anyone know how to sort rows alphabetically by whats in column A for excel?

  • #2
    Re: Sort rows alphabetically by whats in colum A

    Don't really understand how is this related to scripting...
    Anyway, to sort in Excel:
    - pick the range to be sorted (see attached excel1.jpg);
    - go to Data -> Sort (excel2.jpg);
    - pick the column to sort by from the dropdown. If you have column headers, check the "Header Row" radiobutton and you'll get the columns' name in the dropdown box (excel3.jpg);
    - you are supposed to get a result like in the excel4.jpg screenshot.

    This is what you were looking for?
    Last edited by sorinso; 9th November 2007, 21:28.

    Sorin Solomon


    In order to succeed, your desire for success should be greater than your fear of failure.
    -

    Comment


    • #3
      Re: Sort rows alphabetically by whats in colum A

      It's realated to scripting because I want to do exactly what you just posted in vbscript...I have what it would look like in vba when using the macro recorder:

      Code:
      Sub Macro1()
      '
      ' Macro1 Macro
      ' Macro recorded 2/26/2007 by Spectacor-Complex
      '
      
      '
          Application.WindowState = xlMinimized
          Application.WindowState = xlNormal
          Application.WindowState = xlMinimized
          Application.WindowState = xlNormal
          Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
              OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
              DataOption1:=xlSortNormal
      End Sub
      But I have no clue how to make this work for vbscript.

      Comment


      • #4
        Re: Sort rows alphabetically by whats in colum A

        Oh...
        Sorry I wasted your time with my nonsense. Couldn't understand from your original post what you were looking after...

        Sorin Solomon


        In order to succeed, your desire for success should be greater than your fear of failure.
        -

        Comment


        • #5
          Re: Sort rows alphabetically by whats in colum A

          It's ok I didn't make it clear.

          Comment


          • #6
            Re: Sort rows alphabetically by whats in colum A

            I thought this might work goin off the macro...but gettin errors with the special cells:

            Code:
            objExcel.Range("A:AH").Select
            objExcel.Selection.Specialcells(4).Key1' 4 = Range("A2")
            objExcel.Selection.Specialcells(4).Order' 4 = xlAscending
            objExcel.Selection.Specialcells(4).Header' 4 = xlGuess
            objExcel.Selection.Specialcells(4).OrderCustom' 4 = 1
            objExcel.Selection.Specialcells(4).MatchCase' 4 = False
            objExcel.Selection.Specialcells(4).Orientation' 4 = xlTopToBottom
            objExcel.Selection.Specialcells(4).DataOption1' 4 = xlSortNormal

            Comment


            • #7
              Re: Sort rows alphabetically by whats in colum A

              This works:

              objExcel.Range("A:AH").Sort objExcel.Range("A2")

              Except it still moves my first row which is my headers row.

              Comment


              • #8
                Re: Sort rows alphabetically by whats in colum A

                Please post your table
                Server 2000 MCP
                Development: ASP, ASP.Net, PHP, VB, VB.Net, MySQL, MSSQL - Check out my blog http://tonyyeb.blogspot.com

                ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

                Comment


                • #9
                  Re: Sort rows alphabetically by whats in colum A

                  Here's the answer from another forum...

                  objExcel.Range("A:AH").Sort objExcel.Range("A2"),,,,,,,0 ' 0=xlGuess

                  I don't know how this works, but it does!

                  Comment


                  • #10
                    Re: Sort rows alphabetically by whats in colum A

                    This works for me:

                    Code:
                    ObjExcel.Range("B2:B20").Sort ObjExcel.Range("B2:b20")
                    It only sorts B2:B20
                    Server 2000 MCP
                    Development: ASP, ASP.Net, PHP, VB, VB.Net, MySQL, MSSQL - Check out my blog http://tonyyeb.blogspot.com

                    ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

                    Comment


                    • #11
                      Re: Sort rows alphabetically by whats in colum A

                      BTW, do post the link from the "other" forum. Always wanting to learn new magic to stay ahead of the young pups out there.
                      Cheers,

                      Rick

                      ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

                      2006-2099 R Valstar. This post is offered "as is" for discussion purposes only with no express or implied warranty of any kind including, but not limited to, correctness or fitness for use. Nothing herein shall be construed as advice. Attempting any activity based on information in this post is done at your own risk.

                      Comment


                      • #12
                        Re: Sort rows alphabetically by whats in colum A

                        Here's the link to the other forum:

                        http://www.tek-tips.com/viewthread.c...1339035&page=1

                        Comment


                        • #13
                          Re: Sort rows alphabetically by whats in colum A

                          Use "xlYes" (= 1)
                          http://msdn2.microsoft.com/en-us/lib...fice.11).aspx#
                          http://www.microsoft.com/technet/scr....mspx?mfr=true
                          Code:
                          'set variables
                          sExcelFile = "C:\test.xls"
                          sActiveWrkShtNR = 3
                          sFirstRowExcl = "Yes"  'first active row IS containing fieldnames (header)
                          
                          
                          'on error resume next
                          Const xlAscending = 1
                          Const xlYes = 1  'will exclude the first active row from sorting
                          
                          Set objExcel = CreateObject("Excel.Application")
                          '                               open (path, confirmconversions, readonly)
                          Set objWorkbook = objExcel.Workbooks.Open(sExcelFile, true, false)
                          
                          objExcel.DisplayAlerts = FALSE   'OR = 0
                          objExcel.Visible = TRUE   '? (true is open visible, false is open invisible)
                          
                          objWorkbook.sheets(sActiveWrkShtNR).Select
                          
                          Set objWorksheet = objWorkbook.Worksheets(sActiveWrkShtNR)
                          Set objRange = objWorksheet.Range("A:Z")  'IMPORTANT this range must include all the columns to keep the original lines after sorting!!!!!!
                          Set objRange2 = objExcel.Range("B:B")  'this is (or is the range) the column going to be ordered
                          
                          If LCase(sFirstRowExcl) = "yes" Then
                          objRange.Sort objRange2, xlAscending, , , , , , xlYes
                          Else
                          objRange.Sort objRange2, xlAscending, , , , , , xlNo
                          End If
                          
                          'AUTO SAVE
                          objWorkbook.SaveAs sExcelFile  'This will Override the existing file!!!!!!!
                          
                          objExcel.Quit   'Auto closes the file, this MUST always be used when file opend = invisible!
                          
                          Set objWorkbook = Nothing
                          Set objExcel = Nothing  'finally, release the Excel object from memory
                          wscript.quit
                          \Rem

                          EDIT:
                          nice case example of usage 'xlYes' can be found here in the MS 'script shop':
                          Last edited by Rems; 28th February 2007, 12:29. Reason: added a case example

                          This posting is provided "AS IS" with no warranties, and confers no rights.

                          __________________

                          ** Remember to give credit where credit's due **
                          and leave Reputation Points for meaningful posts

                          Comment

                          Working...
                          X