How to move a row up/down by a hotkey, not overwritting ? Topic is solved

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
hancre
Posts: 241
Joined: 02 Jul 2021, 20:51

How to move a row up/down by a hotkey, not overwritting ?

02 Aug 2021, 00:18

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.
User avatar
flyingDman
Posts: 2776
Joined: 29 Sep 2013, 19:01

Re: How to move a row up/down by a hotkey, not overwritting ?

02 Aug 2021, 00:48

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
hancre
Posts: 241
Joined: 02 Jul 2021, 20:51

Re: How to move a row up/down by a hotkey, not overwritting ?

02 Aug 2021, 02:40

flyingDman wrote:
02 Aug 2021, 00:48
Thanks for your reply. but nothing happens in excel.
User avatar
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 ?

02 Aug 2021, 03:06

hancre 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.
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, Joe
hancre
Posts: 241
Joined: 02 Jul 2021, 20:51

Re: How to move a row up/down by a hotkey, not overwritting ?

02 Aug 2021, 03:21


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 )
User avatar
flyingDman
Posts: 2776
Joined: 29 Sep 2013, 19:01

Re: How to move a row up/down by a hotkey, not overwritting ?

02 Aug 2021, 11:04

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...

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
User avatar
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 ?

02 Aug 2021, 12:08

flyingDman wrote:Safe arrays to the rescue here...
Great stuff, flyingDman...works perfectly!
User avatar
flyingDman
Posts: 2776
Joined: 29 Sep 2013, 19:01

Re: How to move a row up/down by a hotkey, not overwritting ?

02 Aug 2021, 12:31

Thanks @JoeWinograd. Surprising how easy it was to adapt the script from single cell to whole row...
14.3 & 1.3.7
User avatar
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 ?

02 Aug 2021, 12:55

easy...for you. :)
User avatar
boiler
Posts: 16708
Joined: 21 Dec 2014, 02:44

Re: How to move a row up/down by a hotkey, not overwritting ?

02 Aug 2021, 18:16

hancre wrote: When I add the script to another script, it doesn't work.
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.
hancre
Posts: 241
Joined: 02 Jul 2021, 20:51

Re: How to move a row up/down by a hotkey, not overwritting ?

02 Aug 2021, 19:45

flyingDman wrote:
02 Aug 2021, 11:04
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
[Mod edit: [code][/code] tags added; quote tags fixed.]
hancre
Posts: 241
Joined: 02 Jul 2021, 20:51

Re: How to move a row up/down by a hotkey, not overwritting ?

02 Aug 2021, 19:47

JoeWinograd wrote:
02 Aug 2021, 12:55
[Mod edit: quote tags fixed.]
Thanks a lot again to point my mistake. ^^
User avatar
flyingDman
Posts: 2776
Joined: 29 Sep 2013, 19:01

Re: How to move a row up/down by a hotkey, not overwritting ?

02 Aug 2021, 22:47

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
hancre
Posts: 241
Joined: 02 Jul 2021, 20:51

Re: How to move a row up/down by a hotkey, not overwritting ?

03 Aug 2021, 01:15

flyingDman wrote:
02 Aug 2021, 22:47
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.
hancre
Posts: 241
Joined: 02 Jul 2021, 20:51

Re: How to move a row up/down by a hotkey, not overwritting ?

17 Aug 2021, 04:05

flyingDman wrote:
02 Aug 2021, 11:04

Code: 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
hancre
Posts: 241
Joined: 02 Jul 2021, 20:51

Re: How to move a row up/down by a hotkey, not overwritting ?

17 Aug 2021, 04:06

flyingDman wrote:
02 Aug 2021, 11:04
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]
hancre
Posts: 241
Joined: 02 Jul 2021, 20:51

Re: How to move a row up/down by a hotkey, not overwritting ?

17 Aug 2021, 04:08

flyingDman wrote:
02 Aug 2021, 11:04
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]
User avatar
flyingDman
Posts: 2776
Joined: 29 Sep 2013, 19:01

Re: How to move a row up/down by a hotkey, not overwritting ?

17 Aug 2021, 12:17

hancre wrote:
17 Aug 2021, 04:08
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.
Did you try to uncomment the lines that are marked "Optional"
14.3 & 1.3.7
hancre
Posts: 241
Joined: 02 Jul 2021, 20:51

Re: How to move a row up/down by a hotkey, not overwritting ?

17 Aug 2021, 22:12

flyingDman wrote:
17 Aug 2021, 12:17
Did you try to uncomment the lines that are marked "Optional"
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
[Mod edit: [code][/code] tags added.]
User avatar
flyingDman
Posts: 2776
Joined: 29 Sep 2013, 19:01

Re: How to move a row up/down by a hotkey, not overwritting ?

17 Aug 2021, 22:40

I meant: remove the ";~" in front of the lines marked "optional".
14.3 & 1.3.7

Return to “Ask for Help (v1)”

Who is online

Users browsing this forum: Google [Bot], inseption86, narej, Rohwedder and 196 guests