Help W/ Small Macro,please (part 2)

BAS872

New Member
Joined
Sep 17, 2002
Messages
5
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
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.
 

Forum statistics

Threads
1,144,060
Messages
5,722,276
Members
422,420
Latest member
losc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top