Another Macro Please

[Dazzawm]

Is there any way that when I select a range of cells the data will swap places?

i.e if i select the range A1:J5 I want it to change the data over as below on the same sheet


Before

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD> </TD><TD> </TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD> </TD><TD> </TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD> </TD><TD> </TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD> </TD><TD> </TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD> </TD><TD> </TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD></TR></TBODY></TABLE>

After

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD> </TD><TD> </TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD> </TD><TD> </TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD> </TD><TD> </TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD> </TD><TD> </TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD> </TD><TD> </TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD></TR></TBODY></TABLE>

 

[Dazzawm]

But what I want it to do is, I select a range of cells like above run the macro then the data changes place also like above.

 

[syntaxed]

Then you can go to VBE, delete the earlier macro and use this one:

Sub Dazzawm()
Dim Rng1 As Range, Temp
Dim Rng2 As Range
If Selection.Columns.Count > 1 And Selection.Columns.Count Mod 2 = 0 Then
     Set Rng1 = Selection.Resize(, Selection.Columns.Count / 2)
       Set Rng2 = Rng1.Offset(, Selection.Columns.Count / 2)
            Temp = Rng1.Value
                Rng1 = Rng2.Value
                    Rng2 = Temp
End If
End Sub

This macro is essentially the same thing but won't run automatically whenever your selection changes.

 

[Dazzawm]

Thanks thats almost correct but I will have 2 blank columns inbetween the data like the table at the top

 

[syntaxed]

Try this:

Sub Dazzawm()
Dim Rng1 As Range, Temp
Dim Rng2 As Range
If Selection.Columns.Count > 2 And Selection.Columns.Count Mod 2 = 0 Then
     Set Rng1 = Selection.Resize(, Selection.Columns.Count / 2 - 1)
       Set Rng2 = Rng1.Offset(, Selection.Columns.Count / 2 + 1)
            Temp = Rng1.Value
                Rng1 = Rng2.Value
                    Rng2 = Temp
End If
End Sub

 

[Dazzawm]

Works perfect thanks syntaxed.

 

[MickG]

The Code I have sent is a "SelectionChange_Event" Macro and is held in the WorkSheet Module.
If you right click you sheet and Select "View Code" you will see the "SelectionChange_ Events" Code.
To run the Code You only need to select the range of cells you wish to swap over.
Once you have selected a range with an Even Number of columns, the code will run.
Unfortunately this will happed every time you select an Even number of columns in a range.
If you want the code to run as a module you can select or from a command Button let me know.
Mick