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

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
fester62DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Not inbuilt, maybe with VBA code
0
Roy CoxGroup Finance ManagerCommented:
You can actually select the ListColumn and just drag and drop the column to the new position.
0
Martin LissOlder than dirtCommented:
Sub MoveCol3ToCol1()

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

Open in new window

0
Determine the Perfect Price for Your IT Services

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

Roy CoxGroup Finance ManagerCommented:
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

0
Roy CoxGroup Finance ManagerCommented:
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

0
fester62DeveloperAuthor 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.
0
Roy CoxGroup Finance ManagerCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fester62DeveloperAuthor Commented:
I'll solve it myself, no worries.
0
Roy CoxGroup Finance ManagerCommented:
Rude!
0
Roy CoxGroup Finance ManagerCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.