Solved

Excel97 - VBA to swap data in columns or rows

Posted on 2002-04-17
22
683 Views
Last Modified: 2012-05-07
I would like to be able to swap data about quickly.
Say I have data in column C and Column F and I wish the data to swap places. Manually I would cut the data in one column, say col C, temporally park it in a spare column, then cut Col F data and paste in col C. Finally cutting the parked data and pasting into col F.
It would be good if a piece of VBA could automate this process.
The user being asked to specify the two data ranges and then just press a button marked say "Interchange" and the data swaps places.  If the VBA could accommodate both rows and columns and even blocks of data (i.e. data covering more than one row or col) then that would be great.
0
Comment
Question by:BlueFin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 5
  • +1
22 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 6948312
Hi BlueFin,

This will not work for formatting, formulas only plain values

Sub Swap()
  Dim R1 As Range, R2 As Range, v As Variant
  If TypeName(ActiveSheet)<>"Worksheet" Then
    MsgBox "Please activate a worksheet"
    Exit Sub
  End If
  If TypeName(Selection)<>"Range" Then
    MsgBox "Please select a range"
    Exit Sub
  End If
  If Selection.Areas.Count <> 2 Then
    MsgBox "Please select a range comprising 2 separate ranges"
    Exit Sub
  End If
  Set R1 = Selection.Areas(1)
  Set R2 = Selection.Areas(2).Resize(R1.Rows.Count, R1.Columns.Count)
  v = R2.Value
  R2.Value = R1.Value
  R1.Value = v
End Sub
 
a snippet from

Bill Manville
Microsoft MVP - Excel

if you want the rest of the goodies i'll have to work myself or since Ture has dropped in he can adjust this one to include formatting etc..

:O)Bruintje
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6949091
Nice!
But that code assumes that two selection ranges are of same size.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6949111
Richie, indeed but the spec was

>>If the VBA could accommodate both rows and columns and even blocks of data  (i.e. data covering more than one row or col) then that would be great

no mention of uneven range sizes

there is more with this code it works only

-for values
-and it's a function so no GUI

J.Walker got a Power Pack on his site where i saw a little nugget like this, i could pay $20 for the source but not sure if it would take the limitations of this solution away

:O)Bruintje
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6949167
That's OK. I tried with two columns, one with 4 rows and other with 3 rows. It fails if you not select the same amount of rows.
Just a clarification.
Cheers
0
 

Author Comment

by:BlueFin
ID: 6949890
bruintje
Thanks for the code - it works fine. Is there any chance to get formula's/ formating to swap too?
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6949897
HI BlueFin,

knew you where going to ask that :)

but i've some workload and dreading things to take care of first....so maybe i can come with something usefull later

:O)Bruintje
0
 
LVL 45

Expert Comment

by:aikimark
ID: 6985009
I'll leave the selection of the columns (based upon the date criteria) to you.  I recorded the following macro with relative recording enabled.  

    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.Cut
    ActiveCell.Offset(0, -3).Columns("A:A").EntireColumn.Select
    Selection.Insert Shift:=xlToRight
    ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
    Selection.Cut
    ActiveCell.Offset(0, 3).Columns("A:A").EntireColumn.Select
    Selection.Insert Shift:=xlToRight

In this example, I did the following:
1. selected the "F" column.
2. right-mouse drag the selected column to the "C" column.
3. selected "shift right" from the pop-up menu.
4. selected the (right-shifted) "C" column.
5. right-mouse dragged the selected column to the right of the original "E" column.
6. selected "shift right" from the pop-up menu.

good luck
0
 

Author Comment

by:BlueFin
ID: 6985620
Thanks aikimark.
But your macro is very specific. I need something that I can use by simply clicking on a block of cells (any block) and highlighting where I want it to go. Bruintje's solution does this fine - the only problem is that formula's get converted to values. I think Bruintje's gone cold on this - but his solution deserves the points as it stands. If I don't hear from Bruintje over the next few day's I'll accept his current answer.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 6985901
If you truly want a safe swap, you will need to add a (hidden) worksheet for your code to use as intermediate storage.  That way, you can transfer all cell properties, not just the value.

In Bruintje's solution, change the "v" variable to a range in the hidden worksheet.

Good luck
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6986094
not gone cold on it, and if my solution doesn't answer the question you certainly shouldn't accept it

i'm busy today but tomorrow i'll try to come up with something from aikimarks comments
0
 

Author Comment

by:BlueFin
ID: 6986113
Thanks aikimark
I've had a try at your suggestion but as I'm no good at VBA I can't get it work.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6986578
Little modification to bruintje's code.

i dont know if this could be useful:
'NOTE: If this code works for you, please don't accept this comment as an answer

Public Sub Swap()
 Dim R1 As Range, R2 As Range, v As Variant
 If TypeName(ActiveSheet) <> "Worksheet" Then
   MsgBox "Please activate a worksheet"
   Exit Sub
 End If
 If TypeName(Selection) <> "Range" Then
   MsgBox "Please select a range"
   Exit Sub
 End If
 If Selection.Areas.Count <> 2 Then
   MsgBox "Please select a range comprising 2 separate ranges"
   Exit Sub
 End If
 Set R1 = Selection.Areas(1)
 Selection.Areas(1).Copy
 With R1
    Set R2 = Selection.Areas(2).Resize(.Rows.Count, .Columns.Count)
    v = R2.Value
    R2.Value = .Value
    .Value = v
    R2.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
End With
End Sub
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6986582
Note that you need to change
Selection.Areas(1).Copy
to the area that you want to get the format and replace R2 for R1 or viceversa.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 6987729
application.ScreenUpdating=False
application.ActiveWorkbook.worksheets.Add  after:=activeworkbook.Worksheets(activeworkbook.Worksheets.Count)
activesheet.visible=false
sheets(1).select
application.ScreenUpdating=true

==============================================
define a worksheet variable used to keep track of the current worksheet and the invisible (temporary) worksheet.
0
 

Author Comment

by:BlueFin
ID: 6988322
Richie  - thanks I tried your code but it still copies formulas to values.
aikimark - could you show me where to put the code. I've tried replacing all occurances of "v" with your code but of course this is not correct. Any chance of getting the full picture?
Thanks
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6991601
"...
thanks I tried your code but it still copies formulas to values...."
Sorry, i am lost, what do you need specially?
0
 

Author Comment

by:BlueFin
ID: 6993740
Ritchie
Say I have the values 1,2,3,4 in cells A1-A4 and I have the formulas =A1+1,=A2+1,=A3+1,=A4+1 in cells B1-B4 - the formulas displaying the values 2,3,4,5 respectively. When I use the "Swap" macro sure enough the values get swapped but instead of the formula =B1+1 appearing in cell A1 I get the value "2" alone - the formula has been overwritten with the value only.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 6993869
In order to preserve your formulas, you will have to effect a "move" operation.  Simple copy/paste (alone) won't work, since formulas are updated.

To do this with copy/paste, you must "fix" the formulas' addresses from relative to absolute before the copy/paste operation.
0
 

Author Comment

by:BlueFin
ID: 6994056
aikimark
I get the general idea - but I need the code to be written for me. I'm no VBA programmer.
0
 
LVL 44

Accepted Solution

by:
bruintje earned 100 total points
ID: 7133518
Hi BlueFin,

maybe this is more what you need

Sub Swap()
 Dim R1 As Range, R2 As Range, v As Variant
 If TypeName(ActiveSheet) <> "Worksheet" Then
   MsgBox "Please activate a worksheet"
   Exit Sub
 End If
 If TypeName(Selection) <> "Range" Then
   MsgBox "Please select a range"
   Exit Sub
 End If
 If Selection.Areas.Count <> 2 Then
   MsgBox "Please select a range comprising 2 separate ranges"
   Exit Sub
 End If
 Set R1 = Selection.Areas(1)
 Set R2 = Selection.Areas(2).Resize(R1.Rows.Count, R1.Columns.Count)
 v = R2.FormulaLocal
 R2.FormulaLocal = R1.FormulaLocal
 R1.FormulaLocal = v
 
End Sub

but beware the formula can be b1*d1 but swapping b1 with d1 or some other variant of using the column in one of the formula's would give you a circular reference, didn't test it completely since i was only looking to close old questions

if you need some more on this let me know then i will put some more time into it

HAGD:O)Bruintje
0
 

Author Comment

by:BlueFin
ID: 7137456
Bruintje - great stuff - for some reason I didn't see the e-mail alert on this. I was housekeeping and was going to close this one off by giving you the points anyway - the swap including formulas will be a great help.
Many thanks
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7137508
Glad you found it, i decided to do the same cleanup and try to solve a few i know wouldn't be that hard only tehy tend to need some focus when working on it

thanks for the points
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show you how to use shortcut menus in the Access run-time environment.
My experience with Windows 10 over a one year period and suggestions for smooth operation
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question