View Single Post
 
Old 07-24-2014, 12:24 PM
RajaP RajaP is offline
Newcomer
 
Join Date: Jul 2005
Posts: 11
Default Sorting Excel range from within VB2013

I am trying to sort an excel range from within VB and it is not working. Intellisense does not give an error, but trying to run does. Below is the sorting part that is not working. I am using Visual Studio 2013, VB module and Excel 2013. I do some calculations and write to book1.xls and create a table in B51 to H132. I want to sort the table by column G. Below is my code. It stops on Sort command and says "The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank."
Code:
      Dim r1 As Range
        sht1.Range("B51").Select()
        r1 = sht1.Range("B51").End(Microsoft.Office.Interop.Excel.XlDirection.xlDown).End(Microsoft.Office.Interop.Excel.XlDirection.xlToRight)


        Dim xlSortOnValues As Long
        Dim xlDescending As Long
        Dim xlSortNormal As Long
        Dim xlSortRows As Long
        Dim xlPinYin As Long
        xlSortOnValues = 0
        xlDescending = 1
        xlSortNormal = 0
        xlSortRows = 1
        xlPinYin = 1
        Dim table As Microsoft.Office.Interop.Excel.ListObject
        table = sht1.ListObjects.Add(Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcRange, _
        r1, , Microsoft.Office.Interop.Excel.XlYesNoGuess.xlGuess)
        table.Name = "CFMLIST"
        Dim sortBy As Range
        sortBy = sht1.Range("G1").End(XlDirection.xlDown)


        r1.Sort(Key1:=sortBy, _
Order1:=Microsoft.Office.Interop.Excel.XlSortOrder.xlDescending, _
Header:=Microsoft.Office.Interop.Excel.XlYesNoGuess.xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=Microsoft.Office.Interop.Excel.XlSortOrientation.xlSortColumns, _
SortMethod:=Microsoft.Office.Interop.Excel.XlSortMethod.xlStroke, _
DataOption1:=Microsoft.Office.Interop.Excel.XlSortDataOption.xlSortNormal)
What is the correct way to sort the range? How can I avoid having to repeatedly type Microsoft.Office.Interop.Excel.....? Thanks in advance for the help.

Last edited by Cerian Knight; 07-25-2014 at 03:50 AM. Reason: Added [code]...[/code] tags
Reply With Quote