Sort range in ascending order

[sharky12345]

Can someone show me how to sort a specific range into ascending order using VBA?

My range is A4:D29 - column A is the range I need sorting. I need rows A1:A3 left unchanged.

 

[Joe4]

If you turn on the Macro Recorder and record yourself performing the steps you want it to do manually, it should give you the VBA code that you are looking for.

 

[MARK858]

Maybe...

Sub sortit()
Range("A4:D29").Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlNo
End Sub

or

Sub sortit()
Range("A4:D29").Sort Range("A4"), 1, , , , , , 2
End Sub

or just

Sub sortit()
Range("A4:D29").Sort Range("A4"), 1
End Sub

 

[sharky12345]

I've tried that - as soon as I click custom sort it selects the whole of column A - I can't work out how to select the range from A4.

 

[sharky12345]

Guys - thanks to both, MARK858 - your first suggestion does the trick.

Thanks.

 

[MARK858]

You're welcome (but all 3 codes should do the same).

Btw the macro recorder gives me

    Range("A4:D29").Select
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("A4:A29") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange Range("A4:D29")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Except I changed xlguess to xlno

 

[sharky12345]

Ahh, that is probably why I couldn't do it - thanks!

 

[Joe4]

Except I changed xlguess to xlno

Well, that is rather annoying!
When you do it manually, there is a checkbox that says "My Data Has Headers".
You would think that if you left it blank, it would choose "xlNo" instead of "xlGuess"!

 

[MARK858]

Well, that is rather annoying!
When you do it manually, there is a checkbox that says "My Data Has Headers".
You would think that if you left it blank, it would choose "xlNo" instead of "xlGuess"!

I am afraid it has always done the same since the updates in Excel 2007

Personally if coding (unless I need any of the newer options), I still prefer to use the old pre 2007 syntax where the default for headers is xlno (I can't remember what the recorder gave you in 2003 and earlier).