Is there an efficient way to move a listcolumn to a different position in a table?

fester62
fester62 used Ask the Experts™
on
I have a table and under certain conditions there is a need to move columns around - is there a nice way to do this using the table syntax and avoiding copying/pasting the data - i.e. some sort of move operation as can be done with sheet columns
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
Not inbuilt, maybe with VBA code
Roy CoxGroup Finance Manager

Commented:
You can actually select the ListColumn and just drag and drop the column to the new position.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Sub MoveCol3ToCol1()

Columns("C:C").Cut
Columns("A:A").Insert Shift:=xlToRight
End Sub

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Roy CoxGroup Finance Manager

Commented:
This will move listcolumn 2 to the 4 column position.

Option Explicit
Sub MoveListColumn()
'
    Dim oTbl As ListObject
    Set oTbl = ActiveSheet.ListObjects(1)

    oTbl.ListColumns(2).Range.Select
    Selection.Cut
    oTbl.HeaderRowRange(4).Select
    Selection.Insert Shift:=xlToRight
End Sub

Open in new window

Roy CoxGroup Finance Manager

Commented:
This adds the ability for the user to  select the column to move and the destination position

Option Explicit
Sub MoveListColumn()
'
    Dim oTbl As ListObject
    Dim iX As Integer, iY As Integer


    Set oTbl = ActiveSheet.ListObjects(1)
    On Error GoTo exit_proc
    iX = Application.InputBox(prompt:="Column to move", Type:=1)
    iY = Application.InputBox(prompt:="Position to move to", Type:=1)

    oTbl.ListColumns(iX).Range.Select
    Selection.Cut
    oTbl.HeaderRowRange(iY).Select
    Selection.Insert Shift:=xlToRight
    Exit Sub
exit_proc:
    MsgBox "cancelled"
End Sub

Open in new window

fester62Developer

Author

Commented:
Cant have anything to do with sheets  or user input. No need to select any objects. It must move ListColumns (not sheet columns) a specified number of columns left or right, not always one.
Group Finance Manager
Commented:
How do you decide what is needed?

What do you mean it can't have anything to do with sheets? You need to define which sheet.

My code moves ListColumns not whole columns.

I've removed the selection, that was just demo code and needed work


Sub MoveListColumn()
'
    Dim oTbl As ListObject
    Dim iX As Integer, iY As Integer


    Set oTbl = ActiveSheet.ListObjects(1)
    On Error GoTo exit_proc
    iX = Application.InputBox(prompt:="Column to move", Type:=1)
    iY = Application.InputBox(prompt:="Position to move to", Type:=1)

    oTbl.ListColumns(iX).Range.Cut
    oTbl.HeaderRowRange(iY).Insert Shift:=xlToRight
    Exit Sub
exit_proc:
    MsgBox "cancelled"
End Sub

Open in new window

fester62Developer

Author

Commented:
I'll solve it myself, no worries.
Roy CoxGroup Finance Manager

Commented:
Rude!
Roy CoxGroup Finance Manager

Commented:
Posted by the author

Cant have anything to do with sheets  or user input. No need to select any objects. It must move ListColumns (not sheet columns) a specified number of columns left or right, not always one.

These comments do not make sense, the sheet is used to define the ListObject, which is not selected.
My code moves ListColumns only, will not affect other data in the sheet
User input defines the number of columns to move and where to, necessary becuase there is no defined columns

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial