CODEWITHSUNDEEP
excelvbadate
Maram Mohd
Maram Mohd

Reputation: 5

VBA Code - Custom Sorts all Columns EXCEPT Date Column

First post on SO, and would hardly consider myself a coder, so this is probably easy... I have been struggling with this code for a while, tt seems to work for all columns except F, which is populated with dates in the format mm/dd/yyyy. The code doesn't execute completely when it reaches column F with dates.

To get this code, I used excel macro recorder and personalized the ranges. 

Sub Sorting()
'
' Sorting Macro
'

'
    Range("A4").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Ambulatory Care").Sort.SortFields.Clear
Dim lastrow As Long
lastrow = Cells(Rows.Count, 2).End(xlUp).Row
ActiveWorkbook.Worksheets("Ambulatory Care").Sort.SortFields.Add Key:=Range( _
    "A5:A" & lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
ActiveWorkbook.Worksheets("Ambulatory Care").Sort.SortFields.Add Key:=Range( _
    "B5:B" & lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
ActiveWorkbook.Worksheets("Ambulatory Care").Sort.SortFields.Add Key:=Range( _
    "C5:C" & lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
ActiveWorkbook.Worksheets("Ambulatory Care").Sort.SortFields.Add Key:=Range( _
    "I5:I" & lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
ActiveWorkbook.Worksheets("Ambulatory Care").Sort.SortFields.Add Key:=Range( _
    "F5:F & lastrow"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("Ambulatory Care").Sort
    .SetRange Range("A4:J798")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Range("A4").Select
End Sub

Upvotes: 0

Views: 188

Answers (1)

AJD
AJD

Reputation: 2438

This is two questions in one.

[It] seems to work for all columns except F

Your code has Key:=Range("F5:F & lastrow"), instead of Key:=Range("F5:F" & lastrow), (note change of position of the ").

any advice on how to make .SetRange Range("A4:J798") dynamic IE A4:last row?

Use the same technique as you have done previously: .SetRange Range("A4:J" & lastrow)

Some additional points

There are some additional learning opportunities here. The use of the macro recorder is a good technique to understand what functions are used and what arguments are usually passed to these functions. But the recorder is not subtle and it records everything, rather than develop good code.

As you would have found, trying to tailor this recorded code can be difficult - what is recorded may not be required in a macro. So I have taken the liberty of rewriting your code (based on what is recorded) with some comments. I haven't tried to add any new techniques - other commentators may identify different efficient ways of achieving what you want.

Sub Sorting()
'
' Sorting Macro
'
' You don't need the original selection lines. You are working directly with the ranges you want below.
' Good indenting is your friend - it makes the logic and flow easier to see.
' All the work you do is with the one worksheet.
' So to make life easier, I am using a single outer "With" to prevent a lot of retyping. 
    With ThisWorkbook.Worksheets("Ambulatory Care").Sort
        .SortFields.Clear
        Dim lastrow As Long
    ' make sure all your range references are fully qualified. Otherwise Excel will default to what is active at the time (which may not be what you think it is or may change during the running of the code). 
        lastrow = .Cells(.Rows.Count, 2).End(xlUp).Row
        .SortFields.Add Key:=Range( _
            "A5:A" & lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        .SortFields.Add Key:=Range( _
            "B5:B" & lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        .SortFields.Add Key:=Range( _
            "C5:C" & lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        .SortFields.Add Key:=Range( _
            "I5:I" & lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        .SortFields.Add Key:=Range( _
            "F5:F" & lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        .SetRange Range("A4:J" & lastrow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
' Final select not required
End Sub

Now you can look at that code, and work out if you could tidy it up any more. It is easier to read and understand.

Some additional reading:

How to avoid using Select in Excel VBA

How to avoid using .Select, .Activate, ActiveSheet,ActiveCell in my specific vba code?

Excel 2013 VBA alternative to using Activate and Select

Using Worksheet CodeName and Avoiding .Select & .Activate

A lot of the issues created by blindly using the recorded macros can be avoided by the advice provided in the answers to those earlier questions.

Upvotes: 1

Related Questions