| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
row movemwnt
|
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
row movemwnt
|
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
row movemwnt
If you're talking a:m, I believe you mean columns instead of rows. If this
is the range you want to include when doing a column d sort, you have 2 choices: 1) if there is no other data beyond column M, simply highlight the rows (on the left) and perform the sort. Or, 2) if there is data beyond column M that you don't want sorted, highlight the range of cells you want sorted and do the Column D sort. "Curt" wrote: is it possible to have the row (a:m) move when a sort is done on column (d) column (d) sorts numerical. Have tried to block and use with selection to no avail. Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
row movemwnt
Looks like you did a pretty good job of figuring it out, but if it is any
help, here is the question in Klingon: ghaH 'oH DuH Daq ghaj [the] [row] [a:m] vIH ghorgh [a] [sort] ghaH ta'pu' Daq [column] [d] *bracketed words are the original English [ no klingon equivalent] -- Regards, Tom Ogilvy "Dave Peterson" wrote: Make sure you select all the range before you sort--not just the column that you're sorting by. Curt wrote: is it possible to have the row (a:m) move when a sort is done on column (d) column (d) sorts numerical. Have tried to block and use with selection to no avail. Thanks -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
row movemwnt
Rows need to move with the sort numerical done in column (D) data is in rows
16-125 columns (A:M) Selecting by excel does not move the row data "Tom Ogilvy" wrote: Looks like you did a pretty good job of figuring it out, but if it is any help, here is the question in Klingon: ghaH 'oH DuH Daq ghaj [the] [row] [a:m] vIH ghorgh [a] [sort] ghaH ta'pu' Daq [column] [d] *bracketed words are the original English [ no klingon equivalent] -- Regards, Tom Ogilvy "Dave Peterson" wrote: Make sure you select all the range before you sort--not just the column that you're sorting by. Curt wrote: is it possible to have the row (a:m) move when a sort is done on column (d) column (d) sorts numerical. Have tried to block and use with selection to no avail. Thanks -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
row movemwnt
doesn't work with code sort code I received I do not understand at my age it
is a learning problem. Any help would be appreciated as to how to proceed. "Dave Peterson" wrote: Make sure you select all the range before you sort--not just the column that you're sorting by. Curt wrote: is it possible to have the row (a:m) move when a sort is done on column (d) column (d) sorts numerical. Have tried to block and use with selection to no avail. Thanks -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
row movemwnt
I guess it's time to share the code you're using.
Curt wrote: doesn't work with code sort code I received I do not understand at my age it is a learning problem. Any help would be appreciated as to how to proceed. "Dave Peterson" wrote: Make sure you select all the range before you sort--not just the column that you're sorting by. Curt wrote: is it possible to have the row (a:m) move when a sort is done on column (d) column (d) sorts numerical. Have tried to block and use with selection to no avail. Thanks -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
row movemwnt
This line in your code:
Range(cell, cell.Offset(0, 1)).Resize(rng.Count).Sort _ key1:=cell.Offset(0, 1), Order1:=xlAscending, _ key2:=cell, Order2:=xlAscending Says to sort D16:Exx and to leave everything else alone. (xx depends on how many cells under D16 are used.) Try this: Range(cell.offset(0,-3), cell.Offset(0, 10)).Resize(rng.Count).Sort _ key1:=cell.Offset(0, 1), Order1:=xlAscending, _ key2:=cell, Order2:=xlAscending The cell.offset(0,-3) is column A. The cell.offset(0,10) is 10 columns to the right of D16 (column N). And you need to include column N since you're inserting that new helper column in E. Later you delete it. But try this against a copy of your data. Curt wrote: I have data in the rows from column a thru m. This data needs to move with sort. Need sort in numerical sequence 1234567890 or could be alpha I can change my pulldown menu. Also can move sort column to any location. Reason for this sort is to establish position in parade. Don't want people right behind animals. Your assistance will be greatly appreciated by all. Here is the code I got. Hope we can make it work This is my biggest stumpling block for Vet's Parade. Thanks for looking Sub AAAA() Dim cell As Range Dim rng As Range Dim rng1 As Range For Each cell In Range("D16") Set rng = Range(cell, cell.End(xlDown)) cell.Offset(0, 1).EntireColumn.Insert Set rng1 = cell.Offset(0, 1).Resize(rng.Count, 1) rng1.Formula = "=Countif(" & rng(1).Address(1, 1) & _ ":" & rng(1).Address(0, 0) & "," & _ rng(1).Address(0, 0) & ")" rng1.Formula = rng1.Value Range(cell, cell.Offset(0, 1)).Resize(rng.Count).Sort _ key1:=cell.Offset(0, 1), Order1:=xlAscending, _ key2:=cell, Order2:=xlAscending cell.Offset(0, 1).EntireColumn.Delete Next End Sub "Dave Peterson" wrote: I guess it's time to share the code you're using. Curt wrote: doesn't work with code sort code I received I do not understand at my age it is a learning problem. Any help would be appreciated as to how to proceed. "Dave Peterson" wrote: Make sure you select all the range before you sort--not just the column that you're sorting by. Curt wrote: is it possible to have the row (a:m) move when a sort is done on column (d) column (d) sorts numerical. Have tried to block and use with selection to no avail. Thanks -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
row movemwnt
Thank you for such a quick responce. I didn't expect anything till after
weekend Will try Sunday after noon and let you know Again Thank You "Dave Peterson" wrote: This line in your code: Range(cell, cell.Offset(0, 1)).Resize(rng.Count).Sort _ key1:=cell.Offset(0, 1), Order1:=xlAscending, _ key2:=cell, Order2:=xlAscending Says to sort D16:Exx and to leave everything else alone. (xx depends on how many cells under D16 are used.) Try this: Range(cell.offset(0,-3), cell.Offset(0, 10)).Resize(rng.Count).Sort _ key1:=cell.Offset(0, 1), Order1:=xlAscending, _ key2:=cell, Order2:=xlAscending The cell.offset(0,-3) is column A. The cell.offset(0,10) is 10 columns to the right of D16 (column N). And you need to include column N since you're inserting that new helper column in E. Later you delete it. But try this against a copy of your data. Curt wrote: I have data in the rows from column a thru m. This data needs to move with sort. Need sort in numerical sequence 1234567890 or could be alpha I can change my pulldown menu. Also can move sort column to any location. Reason for this sort is to establish position in parade. Don't want people right behind animals. Your assistance will be greatly appreciated by all. Here is the code I got. Hope we can make it work This is my biggest stumpling block for Vet's Parade. Thanks for looking Sub AAAA() Dim cell As Range Dim rng As Range Dim rng1 As Range For Each cell In Range("D16") Set rng = Range(cell, cell.End(xlDown)) cell.Offset(0, 1).EntireColumn.Insert Set rng1 = cell.Offset(0, 1).Resize(rng.Count, 1) rng1.Formula = "=Countif(" & rng(1).Address(1, 1) & _ ":" & rng(1).Address(0, 0) & "," & _ rng(1).Address(0, 0) & ")" rng1.Formula = rng1.Value Range(cell, cell.Offset(0, 1)).Resize(rng.Count).Sort _ key1:=cell.Offset(0, 1), Order1:=xlAscending, _ key2:=cell, Order2:=xlAscending cell.Offset(0, 1).EntireColumn.Delete Next End Sub "Dave Peterson" wrote: I guess it's time to share the code you're using. Curt wrote: doesn't work with code sort code I received I do not understand at my age it is a learning problem. Any help would be appreciated as to how to proceed. "Dave Peterson" wrote: Make sure you select all the range before you sort--not just the column that you're sorting by. Curt wrote: is it possible to have the row (a:m) move when a sort is done on column (d) column (d) sorts numerical. Have tried to block and use with selection to no avail. Thanks -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
row movemwnt
Works like a champ
Thanks so much for the explantion of error. That was a great help understanding. Off to try to figure out mail merge next I and many others do Thank You "Dave Peterson" wrote: This line in your code: Range(cell, cell.Offset(0, 1)).Resize(rng.Count).Sort _ key1:=cell.Offset(0, 1), Order1:=xlAscending, _ key2:=cell, Order2:=xlAscending Says to sort D16:Exx and to leave everything else alone. (xx depends on how many cells under D16 are used.) Try this: Range(cell.offset(0,-3), cell.Offset(0, 10)).Resize(rng.Count).Sort _ key1:=cell.Offset(0, 1), Order1:=xlAscending, _ key2:=cell, Order2:=xlAscending The cell.offset(0,-3) is column A. The cell.offset(0,10) is 10 columns to the right of D16 (column N). And you need to include column N since you're inserting that new helper column in E. Later you delete it. But try this against a copy of your data. Curt wrote: I have data in the rows from column a thru m. This data needs to move with sort. Need sort in numerical sequence 1234567890 or could be alpha I can change my pulldown menu. Also can move sort column to any location. Reason for this sort is to establish position in parade. Don't want people right behind animals. Your assistance will be greatly appreciated by all. Here is the code I got. Hope we can make it work This is my biggest stumpling block for Vet's Parade. Thanks for looking Sub AAAA() Dim cell As Range Dim rng As Range Dim rng1 As Range For Each cell In Range("D16") Set rng = Range(cell, cell.End(xlDown)) cell.Offset(0, 1).EntireColumn.Insert Set rng1 = cell.Offset(0, 1).Resize(rng.Count, 1) rng1.Formula = "=Countif(" & rng(1).Address(1, 1) & _ ":" & rng(1).Address(0, 0) & "," & _ rng(1).Address(0, 0) & ")" rng1.Formula = rng1.Value Range(cell, cell.Offset(0, 1)).Resize(rng.Count).Sort _ key1:=cell.Offset(0, 1), Order1:=xlAscending, _ key2:=cell, Order2:=xlAscending cell.Offset(0, 1).EntireColumn.Delete Next End Sub "Dave Peterson" wrote: I guess it's time to share the code you're using. Curt wrote: doesn't work with code sort code I received I do not understand at my age it is a learning problem. Any help would be appreciated as to how to proceed. "Dave Peterson" wrote: Make sure you select all the range before you sort--not just the column that you're sorting by. Curt wrote: is it possible to have the row (a:m) move when a sort is done on column (d) column (d) sorts numerical. Have tried to block and use with selection to no avail. Thanks -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
