.Sort VBA

[AndrewMD]

Hey All,

I'm trying to figure out why this won't work. Basically I want to store a range in a variable and and the use this variable in when using .sort.

I've tried a few things like storing rngr as a range, not using quotes. I'm at a bit of a dead end.

Sub New()

    Dim rngr As String
        
    rngr = Range("A2", Range("A2").End(xlDown).End(xlToRight))
    
    ActiveWorkbook.Worksheets("XAQ").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("XAQ").Sort Key:=Range("rngr"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("XAQ").Sort
        .SetRange Range("rngr")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End Sub

 

[Marcelo Branco]

Maybe this...

Assumes headers in row 2
Key =column a

Sub aSort()
    Dim rngr As [COLOR=#ff0000]Range[/COLOR]
    
    With ActiveWorkbook.Sheets("XAQ")
        Set rngr = .Range("A2", .Range("A2").End(xlDown).End(xlToRight))
    End With
    
    With rngr
        .Sort key1:=.Cells(1), order1:=xlAscending, [B]Header:=xlYes[/B]
    End With
End Sub

Hope this helps

M.

 

[AndrewMD]

Thanks Marcelo, that nearly works but the only problem I have is that it just sorts column A & B and then the rest of the data is out of line. Any ideas?

 

[Marcelo Branco]

Thanks Marcelo, that nearly works but the only problem I have is that it just sorts column A & B and then the rest of the data is out of line. Any ideas?

It should work. Could you post a small data sample?

M.

 

[Marcelo Branco]

Worked for me

Before macro

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Names​

[/td][td]

Values​

[/td][td]

Dept​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Mary​

[/td][td]

10​

[/td][td]

D1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

John​

[/td][td]

7​

[/td][td]

D3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Anthony​

[/td][td]

9​

[/td][td]

D2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Charles​

[/td][td]

11​

[/td][td]

D1​

[/td][/tr]
[/table]

After macro

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Names​

[/td][td]

Values​

[/td][td]

Dept​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Anthony​

[/td][td]

9​

[/td][td]

D2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Charles​

[/td][td]

11​

[/td][td]

D1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

John​

[/td][td]

7​

[/td][td]

D3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Mary​

[/td][td]

10​

[/td][td]

D1​

[/td][/tr]
[/table]

M.

 

[AndrewMD]

Looks like it something to do with what happens before the aSort macro is called.

Sub NewCust()

    Dim cust, status As String

    airline = Worksheets("Dashboard").Range("C13").Value
    watchstatus = Worksheets("Dashboard").Range("C16").Value
    


    Sheets("XAQ").Select
   
        
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1).Select
    ActiveCell.Value = cust
    
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = status
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .Font.Size = 10
        .Font.FontStyle = "Arial"
    End With


    Call aSort
    
End Sub


Sub aSort()
    Dim rngr As Range
    
    With ActiveWorkbook.Sheets("XAQ")
        Set rngr = .Range("A2", .Range("A2").End(xlDown).End(xlToRight))
    End With
    
    With rngr
        .Sort key1:=.Cells(1), order1:=xlAscending, Header:=xlYes
    End With
End Sub

The table is something along the lines of the below. It looks like when a new customer is inserted it just looks to one column to the right and sorts the first two columns (something to do with New only have data in December?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]01-12-2018[/TD]
[TD]01-11-2018[/TD]
[TD]01-10-2018[/TD]
[TD]01-09-2018[/TD]
[/TR]
[TR]
[TD]Alan[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Michael[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Barry[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]New[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Try (assumes headers in row 2)

Sub aSortV2()
    Dim lr As Long, lc As Long
    Dim rngr As Range
    
    With ActiveWorkbook.Sheets("XAQ")
        'last row with data
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        'last column with data
        lc = .Cells(2, .Columns.Count).End(xlToLeft).Column
        Set rngr = .Range("A2", .Cells(lr, lc))
    End With
    
    With rngr
        .Sort key1:=.Cells(1, 1), order1:=xlAscending, Header:=xlYes
    End With
End Sub

M.

 

[AndrewMD]

That did the trick Marcelo, thank you so much!

 

[Marcelo Branco]

You're welcome. Glad to help.

M.