sort a column

[Withnails]

hello
i am looking to sort a data table by column I. the amount of rows in column
I will change over time. when i record this macro it send up an error
(application or object is not defined).
this all happens on sheet two of the worksheet in question.
Can you help and let me know why this error is happening, i'm at a loss?!
thank you - philip

PS: code i have been using is...
Sheets("Sheet2").Select
Cells.Select
Range("A1").Activate
Selection.Sort Key1:=Range("I2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

 

[Joel]

This should work. There was noting selected. I'm sorting the entire row
using column I to determine the last row and assuming there is a header row.

With Sheets("Sheet2")
LastRow = .Range("I" & Rows.Count).End(xlUp).Row
Rows("1:" & LastRow).Sort _
Key1:=.Range("I2"), _
Order1:=xlDescending, _
Header:=xlYes
End With

 

[Dave Peterson]

Your code is in a different (not Sheet2) worksheet module?

That means that the unqualified ranges (Cells (in Cells.select) and range("A1"))
both refer to the sheet that owns the code.

And you can't select or activate a range on a sheet that isn't selected.

You could use:

sheets("sheet2").select
sheets("sheet2").cells.Select
sheets("sheet2").range("A1").activate
and
..., Key1:=sheets("sheet2").Range("I2"), ...

But it's better to just drop all the .select and activates from your code. I
bet you'll find it easier to understand/update later.

With Worksheets("Sheet2")
With .UsedRange
.Sort Key1:=.Columns(9), Order1:=xlDescending, _
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End With

And since you know your data, don't let excel guess at the headers. Either you
have them (xlYes) or you don't (xlNo). (I changed it to xlYes.)

 

[Rick Rothstein]

Following up on Dave's suggestion to "drop the select and activates from
your code", perhaps this previous posting of mine (a response to another
person using Select/Selection type constructions) will be of some help to
you in your future programming (note, you can replace the Select property
with the Activate property in the examples below and everything will still
apply)...

Whenever you see code constructed like this...

Range("A1").Select
Selection.<whatever>

you can almost always do this instead...

Range("A1").<whatever>

In your particular case, you have this...

Range("C2:C8193").Select 'select cells to export
For Each r In Selection.Rows

which, using the above concept, can be reduced to this...

For Each r In Range("C2:C8193").Rows

Notice, all I have done is replace Selection with the range you Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range... and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does not
change.