EXCEL VBA Sort after Populating Data

Question

I am new to VBA, and excel macros, but not basic programming. I have a few dozen excel files, that I am taking data from, cleaning it, and populating it into one file. After the data is populated, I'd like to sort it according to Column A. After an 2 hours of playing with it, I just recorded a macro and cut and pasted it into my ButtonCall sub. But I'd like to know why its working and why the solutions I found here, and online would not work for me...

Why does this simple code NOT work:

Set q = ThisWorkbook.Worksheets(2) 
LastRow = q.UsedRange.rows.Count 'q.UsedRange.Row '  - 1 + q.UsedRange.rows.Count 
LastCol = q.UsedRange.Columns.Count 
q.Range("A6:AAA" & LastRow).Sort Key:=q.Columns("A"), Order:=xlDescending

While this modified recorded Macro does?

Set q = ThisWorkbook.Worksheets(2)
LastRow = q.UsedRange.rows.Count 'q.UsedRange.Row '  - 1 + q.UsedRange.rows.Count
LastCol = q.UsedRange.Columns.Count
q.Sort.SortFields.Clear
q.Sort.SortFields.Add Key:=Range("A6:A" & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With q.Sort
    .SetRange Range("A6:AAA" & LastRow)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Any thoughts? Thanks.

Welcome to Stack Overflow. It's always good to add the incorrect output you get from your attempts so it's clear in what way it does not work. Also there are more useful information under the tour page. — Qben
– Qben, Commented Nov 8, 2013 at 11:05

DaveU · Accepted Answer · 2013-11-08 20:00:26Z

1

Your code is using the range.sort method, while the original code is employing the sort object - two different things.

This code will sort "A6" to end of data by column A, using the Range.Sort method.

Sub MySort()
Dim q As Worksheet
Dim r As Range
Set q = ThisWorkbook.Worksheets(2)
'   specify data range from "A6" to end of data
    Set r = q.Range("A6", q.Cells.SpecialCells(xlCellTypeLastCell))
'   Header:=xlNo assumes A6 row is included in data to be sorted
    r.Sort key1:=r(1, 1), Order1:=xlDescending, Header:=xlNo
End Sub

edited Nov 8, 2013 at 20:00

answered Nov 8, 2013 at 17:52

DaveU

1,0822 gold badges14 silver badges27 bronze badges

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

2 Comments

DaveU Over a year ago

@user2968502, I had another look at your question, so modified my answer to include all the data.

DaveU Over a year ago

@user2968502,if this worked for you, could you check this answer, thanks.

Collectives™ on Stack Overflow

EXCEL VBA Sort after Populating Data

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related