Help with Swapping column - VBA

[AlexanderBB]

I've recorded a macro, but I'd like to swap the contents of 2 columns without
having the selection show on screen. (The sheet is displayed in a multi column list box and is not 'active').

I do have the number of rows in a variable.

My thinking is to select each column as a range then swap one for another but I'm not sure how to go about it, or if I'll need to store one column temporarily.

Any help much appreciated, thanks !

 

[Leith Ross]

Hello AlexanderBB,

You correct that you need to temporarily store one of the column's data. Here is macro to swap column "A" with column "D" on the active sheet.

Sub SwapColumns()

    Dim arrTemp As Variant
    Dim colOne As Range
    Dim colTwo As Range
    
        Set colOne = ActiveSheet.UsedRange.Columns("A")
        Set colTwo = ActiveSheet.UsedRange.Columns("D")
        
        arrTemp = colOne.Cells
        colOne.Cells.Value = colTwo.Cells.Value
        colTwo.Cells.Value = arrTemp
        
End Sub

 

[AlexanderBB]

Leith, How can I alter that to a sheet other than the active one?

Also, I'n nearly there with this -

With Worksheets("Sheet3")
.Columns(2).Copy
.Columns(19).Insert Shift:=xlToRight
.Columns(18).Cut
.Columns (2).Paste
End with

But the .Paste is failing. And I can't get the dropdown list to see what options are available

Thanks...

 

[Leith Ross]

Hello Alexander88,

It is a simple change. Here is the revised macro. This will swap columns 2 and 18 on Sheet3.

Sub SwapColumns()

    Dim arrTemp As Variant
    Dim colOne As Range
    Dim colTwo As Range
    Dim Wks As Worksheet
    
        Set Wks = Worksheets("Sheet3")
        
        Set colOne = Wks.UsedRange.Columns(2)
        Set colTwo = Wks.UsedRange.Columns(18)
        
        arrTemp = colOne.Cells
        colOne.Cells.Value = colTwo.Cells.Value
        colTwo.Cells.Value = arrTemp
        
End Sub

 

[AlexanderBB]

Thank you very much Leith - works brilliantly

Cheers, ABB

 

[Leith Ross]

Hello AleanderBB,

You're welcome.

Sláinte (Cheers in Scottish)

 

[AlexanderBB]

Hi Leith,

Folllowing on from yesterday, I've been looking at the line

arrTemp = colOne.Cells

After adding a Watch to arrtemp, I see it's a two dimensional array.
Why is this - and could it be forced to 1 dimension?

If so, I could use the variable elsewhere, with Join. Giving a fast and efficient way to get everything in the column to a string. (At least I could try !)

 

[Leith Ross]

Hello AlexanderBB,

Here is the VBA code to convert a Range Column into a 1-D array and a Range Row into a 1-D Array. Both 1-D arrays will be one based, i.e. the first element is at array index 1.

Sub ConvertColumn()

    Dim Data As Variant
    Dim NewData As Variant
    
      ' Column of Data
        Data = Range("A1:A5").Value
        
          ' Set 1-D array's size to the number of rows present
            ReDim NewData(UBound(Data, 1))
        
          ' Convert the 2-D column into a 2-D row
            Data = WorksheetFunction.Transpose(Data)
            
          ' Load the 1-D array with the rows from the range
            NewData = WorksheetFunction.Index(Data, 1, 0)
        
End Sub

Sub ConvertRow()

    Dim Data As Variant
    Dim NewData As Variant
    
      ' Row of Data
        Data = Range("A1:F1").Value
        
          ' Set the 1-D array's size to the number of columns present
            ReDim NewData(UBound(Data, 2))
        
          ' Load the 1-D array with the columns from range
            NewData = WorksheetFunction.Index(Data, 1, 0)
          
End Sub

 

[AlexanderBB]

Thanks Leith, I've got that all working in my code and it's great!

Jist one wee thing - if Data is just a single cell if doesn't become an array and ubound(data,1) goes into error.

I have a workaround - put the Redim into If IsArray(Data) = true then Redim...

That seem Ok to you ?

Thanks, ABB

Function isArray(Arr1) As Boolean
    On Error GoTo EmptyArrHandle
    isArray = True
    Dim i As Integer
    i = UBound(Arr1)    'If arr1() contains no element an error will occur
    Exit Function
EmptyArrHandle:
    isArray = False
End Function

 

[Rick Rothstein]

Function isArray(Arr1) As Boolean
    On Error GoTo EmptyArrHandle
    isArray = True
    Dim i As Integer
    i = UBound(Arr1)    'If arr1() contains no element an error will occur
    Exit Function
EmptyArrHandle:
    isArray = False
End Function

A couple of things about your code. First, why is the i variable in it... it isn't being returned in any way, so I don't see why you included it. Second, and more important... VB already has an IsArray function built in. Type IsArray in the Immediate window and, with the text cursor on or next to the word, press F1 to see its help file.