Sort range of cells in single column using excel vba macro

Question

I want to sort a range of cells using Excel VBA macro

I have searched many websites but everywhere the examples show how to sort multiple columns with a single key

In example above I want to sort cells A2 to A8, that's it. For this my code is

Range("A2:A8").Sort

But it is giving Runtime error 1004

What is the correct way to do it?

Mick17 · Accepted Answer · 2016-12-13 15:26:36Z

4

Try this

sub test()
Sheets("sheet1").Range("A2:A8").sort key1:=Range("A2:A8"), order1:=xlAscending, Header:=xlNo
End sub

answered Dec 13, 2016 at 15:26

Mick17

1962 silver badges12 bronze badges

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

2 Comments

John Coleman Over a year ago

Range("A2:A8").Sort key1 := Range("A2") is enough. There seems to be an inconsistency in the documentation. All parameters in the Sort method are listed as being optional, which suggests that if you pass no parameters it defaults to what you get when you select a range and hit the sort button -- but it throws an error when you try to invoke it without any of the "optional" parameters.

PIYUSH VAID Over a year ago

Thanks Mick17 and @John Coleman. Finally done. Really, small mistake in documentation took hours of me.

Collectives™ on Stack Overflow

Sort range of cells in single column using excel vba macro

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related