Unable to Sort XLS data using Range.Sort

Question

I have a xl file with about 2000 rows and columns from A to H. I was trying to sort the file based on the column D such that all other columns are also sorted accordingly (expand selection area).

I am very new to Macros and have been doing this small task to save some time on my reporting.

Here's what I tried:

Prompt the user to select a file
Set the columns from A to H
Sort Range as D2
Save the file

As I said, I am new, I have used much of the code from sample examples in the MSDN library. Apart from Sort(), every thing else is working for me.

here's the code

Sub Select_File_Windows()
    Dim SaveDriveDir As String
    Dim MyPath As String
    Dim Fname As Variant
    Dim N As Long
    Dim FnameInLoop As String
    Dim mybook As Workbook
    Dim SHEETNAME As String

    'Default Sheet Name
    SHEETNAME = "Sheet1"

    ' Save the current directory.
    SaveDriveDir = CurDir

    ' Set the path to the folder that you want to open.
    MyPath = Application.DefaultFilePath

    ' Open GetOpenFilename with the file filters.
    Fname = Application.GetOpenFilename( _
            FileFilter:="XLS Files (*.xls),*.xls,XLSX Files (*.xlsx),*.xlsx", _
            Title:="Select a file", _
            MultiSelect:=True)

    ' Perform some action with the files you selected.
    If IsArray(Fname) Then
        With Application
            .ScreenUpdating = False
            .EnableEvents = True
        End With

        For N = LBound(Fname) To UBound(Fname)

            ' Get only the file name and test to see if it is open.
            FnameInLoop = Right(Fname(N), Len(Fname(N)) - InStrRev(Fname(N), Application.PathSeparator, , 1))
            If bIsBookOpen(FnameInLoop) = False Then

                Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(Fname(N))
                On Error GoTo 0

                DoEvents

                If Not mybook Is Nothing Then
                    Debug.Print "You opened this file : " & Fname(N) & vbNewLine

                    With mybook.Sheets(SHEETNAME)

                        'Columns("A:H").Sort Key1:=Range("D2:D2000"), Order1:=xlAscending, Header:=xlYes
                        'Range("A1:H2000").Sort Key1:=Range("D1"), Order1:=xlAscending
                        Columns("A:H").Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlYes

                    End With

                    Debug.Print "Sorter Called"

                    mybook.Close SaveChanges:=True
                End If
            Else
                Debug.Print "We skipped this file : " & Fname(N) & " because it is already open. Please close the data file and try again"
        End If
        Next N
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End If

End Sub


Function bIsBookOpen(ByRef szBookName As String) As Boolean
    On Error Resume Next
    bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

Nothing is working for me. The file stays as is and No update is made to it. I could not understand, what is the newbie mistake I have been making here ?

Please help.

References:

Looks like you forgot to add a dot in front of Column inside With mybook.Sheets(SHEETNAME). — Miqi180
– Miqi180, Commented Nov 7, 2016 at 17:17

SJR · Accepted Answer · 2016-11-07 17:18:39Z

2

It may be as simple as adding a couple of dots (see pentultimate line below)

With mybook.Sheets(SHEETNAME)
    'Columns("A:H").Sort Key1:=Range("D2:D2000"), Order1:=xlAscending, Header:=xlYes
       'Range("A1:H2000").Sort Key1:=Range("D1"), Order1:=xlAscending
       .Columns("A:H").Sort Key1:=.Range("D1"), Order1:=xlAscending, Header:=xlYes
End With

answered Nov 7, 2016 at 17:18

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sanjeev Over a year ago

that was it. the (.) fixed it. I remember doing that as well, but it did not work. Looking now at the code versioning, the mistake I made was not using the With. Thanks SJR!

user6432984 · Accepted Answer · 2016-11-07 17:56:53Z

SJR is correct in saying that your references should be fully qualified inside of the With Statement.

You should simplify your subroutines by extracting large blocks of code into separate subroutines. The fewer tasks that a subroutines handles, the easier it is to read and to debug.

Refactored Code

Sub Select_File_Windows()
    Const SHEETNAME As String = "Sheet1"
    Dim arExcelFiles
    Dim x As Long

    arExcelFiles = getExcelFileArray

    If UBound(arExcelFiles) = -1 Then
        Debug.Print "No Files Selected"
    Else
        ToggleEvents False
        For x = LBound(arExcelFiles) To UBound(arExcelFiles)
            If IsWorkbookOpen(arExcelFiles(x)) Then
                Debug.Print "File Skipped: "; arExcelFiles(x)
            Else
                Debug.Print "File Sorted: "; arExcelFiles(x)
                With Workbooks.Open(arExcelFiles(x))
                    With .Sheets(SHEETNAME)
                        .Columns("A:H").Sort Key1:=.Range("D1"), Order1:=xlAscending, Header:=xlYes
                    End With
                    .Close SaveChanges:=True
                End With
            End If

        Next

        ToggleEvents True
    End If

End Sub

Function IsWorkbookOpen(ByRef szBookName As String) As Boolean
    On Error Resume Next
    IsWorkbookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

Function getExcelFileArray()
    Dim result
    result = Application.GetOpenFilename( _
             FileFilter:="Excel Workbooks, *.xls; *.xlsx", _
             Title:="Select a file", _
             MultiSelect:=True)

    If IsArray(result) Then
        getExcelFileArray = result
    Else
        getExcelFileArray = Array()
    End If
End Function

Sub ToggleEvents(EnableEvents As Boolean)
    With Application
        .ScreenUpdating = EnableEvents
        .Calculation = IIf(EnableEvents, xlCalculationAutomatic, xlCalculationManual)
        .EnableEvents = EnableEvents
    End With
End Sub

This is awesome. Thanks for the rework. The IsWorkbookOpen(arExcelFiles(x)) was throwing a compiler error (bad variable type ref). I fixed it and now my code is better than before. Cheers!

Collectives™ on Stack Overflow

Unable to Sort XLS data using Range.Sort

2 Answers 2

1 Comment

Refactored Code

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Refactored Code

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related