Switching data in two different rows

TheShaunMichael

Board Regular
Joined
Oct 24, 2009
Messages
57
Is there a way to swith the data in two rows without using the insert & delete properties? Possibly creating an array?
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,646
Office Version
365
Platform
Windows
Is there a way to swith the data in two rows without using the insert & delete properties? Possibly creating an array?
Like this?

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> SwapRows()<br>    <SPAN style="color:#00007F">Dim</SPAN> r1Data<br>    <SPAN style="color:#00007F">Dim</SPAN> r1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    r1 = 4: r2 = 7 <SPAN style="color:#007F00">'<-- Rows to swap</SPAN><br>    r1Data = Rows(r1).Value<br>    Rows(r1).Value = Rows(r2).Value<br>    Rows(r2).Value = r1Data<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Last edited:

TheShaunMichael

Board Regular
Joined
Oct 24, 2009
Messages
57
Oh how obvious the answer seems in retrospection.

Let me however add one more layer of complexity. Here's the challenge: I have information in rows 12-15 of my spreadsheet. A userform opens and a listbox is populated with the values of B12:B15. Two command buttons allows the user to move items in the listbox up or down. When the user clicks okay, I then want the corresponding rows to change orders. Does that make sense?

So, i'm thinking of testing for the value in B12:B16 and assigning it to a variable, at which point I should be able to use the code you wrote. Thoughts???
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,646
Office Version
365
Platform
Windows
Sorry, Userforms and their components are not one of my strong points. Hopefully somebody with more expertise in that area will step in.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,114
Messages
5,466,760
Members
406,497
Latest member
Bryanlim

This Week's Hot Topics

Top