sort macro stopped sorting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This sort was working yesterday. This morning it would not sort. I don't
know if it didn't get saved properly. The module compiles. After it
wouldn't sort I had to add a few fields since they changed the speadsheet db.
I just renamed the column numbers of the sort keys that was all I changed.
can you tell me why it doesn't sort. When I run the macro it just sits there
and doesn't move. No response.

Thanks,

-----macro that doesn't sort------


Sub Sort()
'
' Sorts by Item Name, Dept, Status# Macro
Dim rng As Range

' sorts on Dept, & Status since there is only 3 keys available in a sort
With ActiveSheet
Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 29).End(xlUp))

rng.Sort key1:=.Cells(2, 16), Order1:=xlAscending, _
key2:=.Cells(2, 19), Order2:=xlAscending, _
key3:=.Cells(2, 3), Order3:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With


End Sub
 
what is in column 29. If there is no data there, then there is your
huckleberry.

Add this to check

Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 29).End(xlUp))
msgbox rng.Address

if rng just refers to the first row or less than what you expected . . .
 
I ran it, trying to reproduce the error and had no problems. What do
you mean "sits there"?

Charles
 
Since the database changed did the number of columns change? If so your 29
could now be incorrect. Try this if the data source could be changing. It
dynamically determines the number of columns

Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, .cells(1,
columns.count).end(xlToLeft)).End(xlUp))
 
"sits there = doesn't move, i.e. it used to kind of shake a little as it
moved all the fields around. Now it doesn't move at all. The cursor doesn't
move, nothing moves.
 
I love the error checking message box. Thanks.
That was it, the column on the right had data in it but
they added two fields, but they took out some fields also.
It was just the wrong number on the right.
 
Yes the number of columns changed. I didn't know Excel had to have data in
it to count it. I assumed it could count either way. Thanks, for the
expandable column counter!
 
Back
Top