.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

	A​	B​	C​
1​
2​	Names​	Values​	Dept​
3​	Mary​	10​	D1​
4​	John​	7​	D3​
5​	Anthony​	9​	D2​
6​	Charles​	11​	D1​

After macro

	A​	B​	C​
1​
2​	Names​	Values​	Dept​
3​	Anthony​	9​	D2​
4​	Charles​	11​	D1​
5​	John​	7​	D3​
6​	Mary​	10​	D1​

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?

Customer	01-12-2018	01-11-2018	01-10-2018	01-09-2018
Alan	4	4	4	4
Michael	3	3	2	5
Barry	3	2	3	3
New	3

<tbody>
</tbody>

 

[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.