Sort Macro

[CamEns]

I'm having trouble creating a macro that will sort a column of values.
There is information in the first few rows that should not be sorted
and totals and other information in rows after the data that shoul
also not be sorted.

To manually do the command I would anchor the start of the range an
use the 'down arrow' and then the 'end' key. How do I do that in
macro?

The problem is that rows in the middle of the range are always bein
added or deleted so it is a dynamic range. I had no success with th
offset command as it doesn't (or didn't the way I was using it) wor
when you have data below the list that can't be part of the range.

Any ideas

 

[MSP77079]

have you tried turning on the macro recorder and doing what you would d
manually?

If you used the Ctrl key and pressed the down arrow, the macro recorde
would record this:

Selection.End(xlDown).Select


If you have at least one blank row between your data and your total fo
that column, the .End(xlDow) will correctly find the end of the data

 

[Tim Coddington]

Perhaps the expression 'activecell.End(xlDown).Row' would be of some help.
This would return the row number at the bottom of the range. You could then
build a range expression to include the entire area.

 

[CamEns]

I had done that and I was still having trouble getting it to work.
However, after your suggestion I looked at it again and after the .en
command I was expanding the range to the right and that is what locke
the range in. Quick adjustment and now it works.

Thanks