Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Macro
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Macro
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 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Macro
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 " wrote in message ... 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 should also not be sorted. To manually do the command I would anchor the start of the range and use the 'down arrow' and then the 'end' key. How do I do that in a macro? The problem is that rows in the middle of the range are always being added or deleted so it is a dynamic range. I had no success with the offset command as it doesn't (or didn't the way I was using it) work when you have data below the list that can't be part of the range. Any ideas? --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Macro
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 -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort Macro | Excel Discussion (Misc queries) | |||
Macro sort | New Users to Excel | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Using Macro to sort without clicking on macro button | Excel Discussion (Misc queries) | |||
Sort Macro | Excel Programming |