Help W/ Small Macro,please (part 2)

[BAS872]

I would appreciate help with a macro to sort data in a small table.

The table is in range A1:F15

Problem:
Sometimes not all rows contain data. The last one, two or more (up to 6 or 7)rows may be empty. When Excel sorts the whole table it places empty rows on top and it leaves corresponding gaps in the chart associated with the table!

The empty rows are always the ones at the bottom of the table. And, as I say, it may be anywhere between the last one and the last seven rows that will be empty.

With the help of Tom Urtis, I set up the following code to sort only the rows that have data:

Sub SortList()
Dim sList As Range
Set sList = Range(("A1"), Range("F65536").End(xlUp))
sList.Sort Key1:=Range("A2"), Order1:=xl"Descending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

But when I run it I get the message:
"Compile error. Named argument cannot be found"

What am I doing wrong? How can I correct it?

I would appreciate any help. I am not a programmer...as if you couldn't tell!

Thanks

 

[Dave Patton]

On 2002-09-21 10:58, BAS872 wrote:
I would appreciate help with a macro to sort data in a small table.

The table is in range A1:F15

Problem:
Sometimes not all rows contain data. The last one, two or more (up to 6 or 7)rows may be empty. When Excel sorts the whole table it places empty rows on top and it leaves corresponding gaps in the chart associated with the table!

The empty rows are always the ones at the bottom of the table. And, as I say, it may be anywhere between the last one and the last seven rows that will be empty.

With the help of Tom Urtis, I set up the following code to sort only the rows that have data:

Sub SortList()
Dim sList As Range
Set sList = Range(("A1"), Range("F65536").End(xlUp))
sList.Sort Key1:=Range("A2"), Order1:=xl"Descending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

But when I run it I get the message:
"Compile error. Named argument cannot be found"

What am I doing wrong? How can I correct it?

I would appreciate any help. I am not a programmer...as if you couldn't tell!

Thanks
______________

You may have an extraneous " by Descending

You could try Macro Recorder and see what code it creates for your sort.

See following watch for incorrect wrapping

Sub SortList()
Dim sList As Range
Set sList = Range(("A1"), Range("F65536").End(xlUp))
sList.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub
This message was edited by Dave Patton on 2002-09-21 11:46

 

[Tom Urtis]

Take a look at line 4 of your code, and how it differs from what I posted to you yesterday. You changed xlAscending to xlDescending, which is fine, but now there are 2 quotes or apostrophes in there, which were not part of the original code. Remove those characters and see what happens.