Sort problem in Excel 2007

[Andrew Bourke]

Hi
I am testing macros that worked in Excel 2003 under Excel 2007.


In the program I use, rows from a list (across several cells) are
sometimes removed and I then use the sort line below to remove the blank
rows left in the list. This then produces the error message below.


'--- Do an alpha sort to remove blanked out lines
Shd.Range("B5:BB40").Sort Key1:=Shd.Range("B5"), Order1:=xlAscending


Error message:
"sort method of the range class failed error 1004"

Is there a simple way around this ?


Interesting:

1. After I 'End' the error message and get back to the page where the
blank rows are I can then sort the rows OK using a slightly different
sort routine (sorted numerically by total instead of alphabetically).


2. When re-running the code back under Excel 2003 I now get the same
error as first appeared in Excel 2007 !!

 

[Ron Rosenfeld]

Hi
I am testing macros that worked in Excel 2003 under Excel 2007.


In the program I use, rows from a list (across several cells) are
sometimes removed and I then use the sort line below to remove the blank
rows left in the list. This then produces the error message below.


'--- Do an alpha sort to remove blanked out lines
Shd.Range("B5:BB40").Sort Key1:=Shd.Range("B5"), Order1:=xlAscending


Error message:
"sort method of the range class failed error 1004"

Is there a simple way around this ?


Interesting:

1. After I 'End' the error message and get back to the page where the
blank rows are I can then sort the rows OK using a slightly different
sort routine (sorted numerically by total instead of alphabetically).


2. When re-running the code back under Excel 2003 I now get the same
error as first appeared in Excel 2007 !!

I wonder if one of the optional Sort method parameters are somehow getting set
in such a way as to mess things up. Try setting them explicitly and see what
happens.

There were some new sort parameters added in 2007.
--ron

 

[Andrew Bourke]

I've tried a workaround but all of a sudden it started working again -
WEIRD!

Thanks

 

[Ron Rosenfeld]

I've tried a workaround but all of a sudden it started working again -
WEIRD!

I've not tested the Sort method, but I know that with some methods, the
optional parameters, once set, stay at that setting until changed. So I still
wonder if that was the issue. If so, it could recur.
--ron