Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort without Selecting
My current code for Sorting selects the sheet, sorts the data then
returns back to my Data Entry sheet. How can I have this perform this without the code selecting the sheet? I just want it to happen in the background without the sheets flashing back and forth while this happens. I did a search and found some suggestions but can't find one that works for my application. Thanks Jody ' SortcpModels Sheets("cpModels").Select Columns("A:B").Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Sheets("Data Entry").Select Range("B3").Select |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort without Selecting
Jody,
Try Sheets("cpModels").Range("A:B").Sort Key1:=Range("A2"), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "ssjody" wrote in message oups.com... My current code for Sorting selects the sheet, sorts the data then returns back to my Data Entry sheet. How can I have this perform this without the code selecting the sheet? I just want it to happen in the background without the sheets flashing back and forth while this happens. I did a search and found some suggestions but can't find one that works for my application. Thanks Jody ' SortcpModels Sheets("cpModels").Select Columns("A:B").Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Sheets("Data Entry").Select Range("B3").Select |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort without Selecting
Works! Thanks Chip
Jody |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort without Selecting
Hi, Jody,
Thjere are lots of ways you can sort more efficiently, without selecting forst: Sub Macro5() Range("B11:T17").Sort Key1:=Range("B12"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub I find it easier to give range names to header cells in your database, so, id you insert or delete rows, your code always points to the same cell. In the above example, you could replays Range("B12") with Range("SalesColHeader") for example, where SalesColHeader is a name applied to cell B12. I always call the first field header "DatabaseStart", thus the above example would begin: Range("DatabaseStart").Sort Key1 Hope this is of some use Happy Christmas Pete "ssjody" wrote: My current code for Sorting selects the sheet, sorts the data then returns back to my Data Entry sheet. How can I have this perform this without the code selecting the sheet? I just want it to happen in the background without the sheets flashing back and forth while this happens. I did a search and found some suggestions but can't find one that works for my application. Thanks Jody ' SortcpModels Sheets("cpModels").Select Columns("A:B").Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Sheets("Data Entry").Select Range("B3").Select |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort without Selecting
Thanks Pete! That makes good sense. I can use that.
Jody |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort without Selecting
I think this is my current "standard" for sorting.
' SortcpModels with Sheets("cpModels") with .range("a:b") .sort key1:=.columns(1), order1:=xlascending, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal end with end with I like that I can use .columns(1) to specify the key(s). ssjody wrote: My current code for Sorting selects the sheet, sorts the data then returns back to my Data Entry sheet. How can I have this perform this without the code selecting the sheet? I just want it to happen in the background without the sheets flashing back and forth while this happens. I did a search and found some suggestions but can't find one that works for my application. Thanks Jody ' SortcpModels Sheets("cpModels").Select Columns("A:B").Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Sheets("Data Entry").Select Range("B3").Select -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort without Selecting
There is a bug in my code. It should be
Sheets("cpModels").Range("A:B").Sort Key1:=Sheets("cpModels").Range("A2"), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Chip Pearson" wrote in message ... Jody, Try Sheets("cpModels").Range("A:B").Sort Key1:=Range("A2"), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "ssjody" wrote in message oups.com... My current code for Sorting selects the sheet, sorts the data then returns back to my Data Entry sheet. How can I have this perform this without the code selecting the sheet? I just want it to happen in the background without the sheets flashing back and forth while this happens. I did a search and found some suggestions but can't find one that works for my application. Thanks Jody ' SortcpModels Sheets("cpModels").Select Columns("A:B").Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Sheets("Data Entry").Select Range("B3").Select |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting only specific rows for a Sort - additional bit of info.. | Excel Discussion (Misc queries) | |||
Selecting only specific rows for a Sort | Excel Discussion (Misc queries) | |||
Pls. reply Sort Data and copy to next coulmn when sort order chang | Excel Programming | |||
Selecting in groups within a sort | Excel Programming | |||
Can I sort without activating / selecting sheet? | Excel Programming |