I want to move a row up/down by a hotkey.
the action should not overwrite the contents.
ex. I I do thiis action(up) in A19, A18 and A19 would switch the location. ( A18 → A19, A19 → A18 )
How can I get the results?
Thanks for any help in advance.
How to move a row up/down by a hotkey, not overwritting ? Topic is solved
- flyingDman
- Posts: 2776
- Joined: 29 Sep 2013, 19:01
Re: How to move a row up/down by a hotkey, not overwritting ?
I assumed you're working in Excel. Try:
Code: Select all
xl := ComObjActive("excel.application")
#up::
tmp1 := xl.activecell.value
tmp2 := xl.activecell.offset(-1,0).value
xl.activecell.offset(-1,0) := tmp1
xl.activecell := tmp2
;xl.activecell.offset(-1,0).select ; optional
return
#down::
tmp1 := xl.activecell.value
tmp2 := xl.activecell.offset(1,0).value
xl.activecell.offset(1,0) := tmp1
xl.activecell := tmp2
;xl.activecell.offset(1,0).select ; optional
return
14.3 & 1.3.7
Re: How to move a row up/down by a hotkey, not overwritting ?
Thanks for your reply. but nothing happens in excel.
- JoeWinograd
- Posts: 2166
- Joined: 10 Feb 2014, 20:00
- Location: U.S. Central Time Zone
Re: How to move a row up/down by a hotkey, not overwritting ?
You're saying two different things: first comment says you want to switch the whole row, while the second comment says you want to switch only a single cell. Which one do you want? flyingDman's code works perfectly to switch the selected cell...it does not switch the whole row. Regards, Joehancre wrote:I want to move a row up/down by a hotkey.
I do thiis action(up) in A19, A18 and A19 would switch the location.
Re: How to move a row up/down by a hotkey, not overwritting ?
JoeWinograd wrote: ↑
I want to move a row up/down by a hotkey.
Thanks a lot to point my mistake.
I tried the code again. It works well ( switching the two cells) without my other scripts. When I add the script to another script, it doesn't work.
I'll search the reason. If I can't solve it, I'll use this script in another file.
I want to swich move a row up/down, not just switching two cells.
( ex. row 18 → row 19, row 19 → row 18 )
- flyingDman
- Posts: 2776
- Joined: 29 Sep 2013, 19:01
Re: How to move a row up/down by a hotkey, not overwritting ?
I had read it as "move one cell up (or down) one row".
We can still apply the same principle to move an entire row up or down. Safe arrays to the rescue here...
We can still apply the same principle to move an entire row up or down. Safe arrays to the rescue here...
Code: Select all
xl := ComObjActive("excel.application")
#up::
tmp1 := xl.activecell.entirerow.value
tmp2 := xl.activecell.offset(-1,0).entirerow.value ; tmp 1 and tmp2 are safe arrays;msgbox % tmp2[1,2]
xl.activecell.offset(-1,0).entirerow.value := tmp1
xl.activecell.entirerow.value := tmp2
;~ xl.activecell.offset(-1,0).select ; optional
return
#down::
tmp1 := xl.activecell.entirerow.value
tmp2 := xl.activecell.offset(1,0).entirerow.value ; tmp 1 and tmp2 are safe arrays;msgbox % tmp2[1,2]
xl.activecell.offset(1,0).entirerow.value := tmp1
xl.activecell.entirerow.value := tmp2
;~ xl.activecell.offset(1,0).select ; optional
return
14.3 & 1.3.7
- JoeWinograd
- Posts: 2166
- Joined: 10 Feb 2014, 20:00
- Location: U.S. Central Time Zone
Re: How to move a row up/down by a hotkey, not overwritting ?
Great stuff, flyingDman...works perfectly!flyingDman wrote:Safe arrays to the rescue here...
- flyingDman
- Posts: 2776
- Joined: 29 Sep 2013, 19:01
Re: How to move a row up/down by a hotkey, not overwritting ?
Thanks @JoeWinograd. Surprising how easy it was to adapt the script from single cell to whole row...
14.3 & 1.3.7
- JoeWinograd
- Posts: 2166
- Joined: 10 Feb 2014, 20:00
- Location: U.S. Central Time Zone
Re: How to move a row up/down by a hotkey, not overwritting ?
easy...for you.
Re: How to move a row up/down by a hotkey, not overwritting ?
Did you put the first line (ComObjActive) at the top of your script in the auto-execute section? If you have it placed lower in the script, such as below some hotkey definitions, then it will never get executed.
Re: How to move a row up/down by a hotkey, not overwritting ?
Sorry to make you confused.
your new script works very well.
Can I have another qeustion?
I want to apply your code to moving a column left/right by a hotkey, not overwriting .
I'm a beginner of autohotkey.
esp. I don't know how to work autohotkey with excel.
I tried your code to switching column, changing <entirerow> to <entirecolumn>
but it shows errors :
How can I fix the error?
anyway thanks a lot for your help again. I can do excel fastly thanks to your code.
source ~~~~~
Code: Select all
xl := ComObjActive("excel.application")
!g::
tmp1 := xl.activecell.entirerow.value
tmp2 := xl.activecell.offset(-1,0).entirerow.value ; tmp 1 and tmp2 are safe arrays;msgbox % tmp2[1,2]
xl.activecell.offset(-1,0).entirerow.value := tmp1
xl.activecell.entirerow.value := tmp2
;~ xl.activecell.offset(-1,0).select ; optional
return
!h::
tmp1 := xl.activecell.entirerow.value
tmp2 := xl.activecell.offset(1,0).entirerow.value ; tmp 1 and tmp2 are safe arrays;msgbox % tmp2[1,2]
xl.activecell.offset(1,0).entirerow.value := tmp1
xl.activecell.entirerow.value := tmp2
;~ xl.activecell.offset(1,0).select ; optional
return
Re: How to move a row up/down by a hotkey, not overwritting ?
[Mod edit: quote tags fixed.]
Thanks a lot again to point my mistake. ^^
- flyingDman
- Posts: 2776
- Joined: 29 Sep 2013, 19:01
Re: How to move a row up/down by a hotkey, not overwritting ?
Moving columns is complicated by the fact that there are a lot more cells to move. As this technique - using entirecolumn - moves all cells (even the empty cells),it is not terribly efficient and on my old laptop, the lag is not acceptable. So, we need to select the range starting with row 1 and ending with the last row of the "usedrange", not just the last row of the column in question, because if we do that, we will be moving partial columns and create a huge mess. There are several ways to do that, but I like to use "intersect". We are looking for the intersection of the sheet's usedrange and the entirecolumn containing the activecell (or the adjacent cell). Here it goes:
Code: Select all
#left::
if (xl.activecell.column = 1)
return
tmp1 := xl.intersect(xl.activesheet.UsedRange,xl.activecell.EntireColumn).value
tmp2 := xl.intersect(xl.activesheet.UsedRange,xl.activecell.offset(0,-1).EntireColumn).value
xl.intersect(xl.activesheet.UsedRange,xl.activecell.EntireColumn) := tmp2
xl.intersect(xl.activesheet.UsedRange,xl.activecell.offset(0,-1).EntireColumn).value := tmp1
xl.activecell.offset(0,-1).select ; optional
return
#right::
tmp1 := xl.intersect(xl.activesheet.UsedRange,xl.activecell.EntireColumn).value
tmp2 := xl.intersect(xl.activesheet.UsedRange,xl.activecell.offset(0,1).EntireColumn).value
xl.intersect(xl.activesheet.UsedRange,xl.activecell.EntireColumn) := tmp2
xl.intersect(xl.activesheet.UsedRange,xl.activecell.offset(0,1).EntireColumn).value := tmp1
xl.activecell.offset(0,1).select ; optional
return
14.3 & 1.3.7
Re: How to move a row up/down by a hotkey, not overwritting ?
Thanks a lot for your help again.
These also works well.
Last edited by gregster on 03 Aug 2021, 11:23, edited 1 time in total.
Reason: Quote tags fixed again. Don't forget the final [/quote] tag.
Reason: Quote tags fixed again. Don't forget the final [/quote] tag.
Re: How to move a row up/down by a hotkey, not overwritting ?
flyingDman wrote: ↑02 Aug 2021, 11:04Code: Select all
xl := ComObjActive("excel.application") Can I have another question? Nowdays I'm making an english materials. With your script, I handle excel fast. Thanks a lot. But there's another problem. If I make the row up selecting a cell, it switch the location between the selected sell(429) and the upper row(428). After it, the cursor(selecting) is in the 429. If possible, I want to put the cursor in 428. this hotkey makes the disatance between the related words. ex. cover 425, ( 425 ~ 429 mean the number of row. mistake 426 focus 427 protect 428 recover 429. I want to put recover to row 426. The current state needs moving a manual <up> after the automatic switcing <up> This issue also apply to the downside. [Codebox=autohotkey file=Untitled.ahk] #up:: tmp1 := xl.activecell.entirerow.value tmp2 := xl.activecell.offset(-1,0).entirerow.value ; tmp 1 and tmp2 are safe arrays;msgbox % tmp2[1,2] xl.activecell.offset(-1,0).entirerow.value := tmp1 xl.activecell.entirerow.value := tmp2 ;~ xl.activecell.offset(-1,0).select ; optional return #down:: tmp1 := xl.activecell.entirerow.value tmp2 := xl.activecell.offset(1,0).entirerow.value ; tmp 1 and tmp2 are safe arrays;msgbox % tmp2[1,2] xl.activecell.offset(1,0).entirerow.value := tmp1 xl.activecell.entirerow.value := tmp2 ;~ xl.activecell.offset(1,0).select ; optional return
Re: How to move a row up/down by a hotkey, not overwritting ?
Can I have another question?
Nowdays I'm making an english materials.
With your script, I handle excel fast. Thanks a lot.
But there's another problem.
If I make the row up selecting a cell, it switch the location between the selected sell(429) and the upper row(428).
After it, the cursor(selecting) is in the 429.
If possible, I want to put the cursor in 428.
this hotkey makes the disatance between the related words.
ex. cover 425, ( 425 ~ 429 mean the number of row.
mistake 426
focus 427
protect 428
recover 429.
I want to put recover to row 426.
The current state needs moving a manual <up> after the automatic switcing <up>
This issue also apply to the downside.
Code: Select all
#up::
tmp1 := xl.activecell.entirerow.value
tmp2 := xl.activecell.offset(-1,0).entirerow.value ; tmp 1 and tmp2 are safe arrays;msgbox % tmp2[1,2]
xl.activecell.offset(-1,0).entirerow.value := tmp1
xl.activecell.entirerow.value := tmp2
;~ xl.activecell.offset(-1,0).select ; optional
return
#down::
tmp1 := xl.activecell.entirerow.value
tmp2 := xl.activecell.offset(1,0).entirerow.value ; tmp 1 and tmp2 are safe arrays;msgbox % tmp2[1,2]
xl.activecell.offset(1,0).entirerow.value := tmp1
xl.activecell.entirerow.value := tmp2
;~ xl.activecell.offset(1,0).select ; optional
return[/code]
Re: How to move a row up/down by a hotkey, not overwritting ?
Can I have another question?
Nowdays I'm making an english materials.
With your script, I handle excel fast. Thanks a lot.
But there's another problem.
If I make the row up selecting a cell, it switch the location between the selected sell(429) and the upper row(428).
After it, the cursor(selecting) is in the 429.
If possible, I want to put the cursor in 428.
this hotkey makes the disatance between the related words.
ex. cover 425, ( 425 ~ 429 mean the number of row.
mistake 426
focus 427
protect 428
recover 429.
I want to put recover to row 426.
The current state needs moving a manual <up> after the automatic switcing <up>
This issue also apply to the downside.
I change the location very often. So this issue is important for me. Please help me.
Code: Select all
#up::
tmp1 := xl.activecell.entirerow.value
tmp2 := xl.activecell.offset(-1,0).entirerow.value ; tmp 1 and tmp2 are safe arrays;msgbox % tmp2[1,2]
xl.activecell.offset(-1,0).entirerow.value := tmp1
xl.activecell.entirerow.value := tmp2
;~ xl.activecell.offset(-1,0).select ; optional
return
#down::
tmp1 := xl.activecell.entirerow.value
tmp2 := xl.activecell.offset(1,0).entirerow.value ; tmp 1 and tmp2 are safe arrays;msgbox % tmp2[1,2]
xl.activecell.offset(1,0).entirerow.value := tmp1
xl.activecell.entirerow.value := tmp2
;~ xl.activecell.offset(1,0).select ; optional
return[/code]
- flyingDman
- Posts: 2776
- Joined: 29 Sep 2013, 19:01
Re: How to move a row up/down by a hotkey, not overwritting ?
Did you try to uncomment the lines that are marked "Optional"
14.3 & 1.3.7
Re: How to move a row up/down by a hotkey, not overwritting ?
Yes, I did.
I didn't change your code.
Code: Select all
Appskey & q:: ;move a row up
tmp1 := xl.activecell.entirerow.value
tmp2 := xl.activecell.offset(-1,0).entirerow.value ; tmp 1 and tmp2 are safe arrays;msgbox % tmp2[1,2]
xl.activecell.offset(-1,0).entirerow.value := tmp1
xl.activecell.entirerow.value := tmp2
;~ xl.activecell.offset(-1,0).select ; optional
Return
Appskey & w:: ;move a row down
tmp1 := xl.activecell.entirerow.value
tmp2 := xl.activecell.offset(0,1).entirerow.value ; tmp 1 and tmp2 are safe arrays;msgbox % tmp2[1,2]
xl.activecell.offset(1,0).entirerow.value := tmp1
xl.activecell.entirerow.value := tmp2
;~ xl.activecell.offset(1,0).select ; optional
Return
- flyingDman
- Posts: 2776
- Joined: 29 Sep 2013, 19:01
Re: How to move a row up/down by a hotkey, not overwritting ?
I meant: remove the ";~" in front of the lines marked "optional".
14.3 & 1.3.7
Who is online
Users browsing this forum: Google [Bot], inseption86, narej, Rohwedder and 196 guests