none
Macro help to move column RRS feed

  • Question

  • I have a worksheet that contains a table with 5 columns (Student_Name, Student_ID, Course, Course_ID, Current_Grade). I need to move Course_ID into the 2nd position so that I end up with Student_Name, Course_ID, Student_ID, Course, Current_Grade).  I tried Recording Macro, however because it's a table I couldn't select/copy/paste the column elsewhere.  Rather I had to select the contents of the column with then creates a specific range of rows.  This won't work because there may be different numbers of rows from report to report.  How can I edit the code below to move the entire column without specifying the actual row numbers?

    Range("D2").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        ActiveWindow.ScrollRow = 10441
        ActiveWindow.ScrollRow = 10410
        ActiveWindow.ScrollRow = 9446
        ActiveWindow.ScrollRow = 6526
        ActiveWindow.ScrollRow = 5562
        ActiveWindow.ScrollRow = 4133
        ActiveWindow.ScrollRow = 3294
        ActiveWindow.ScrollRow = 2828
        ActiveWindow.ScrollRow = 2735
        ActiveWindow.ScrollRow = 560
        ActiveWindow.ScrollRow = 342
        ActiveWindow.ScrollRow = 1
        Range("B2").Select
        ActiveSheet.Paste


    There are no mistakes; every result tells you something of value about what you are trying to accomplish.


    • Edited by run4it Wednesday, February 26, 2020 5:54 PM
    Wednesday, February 26, 2020 5:53 PM

Answers

  • Try this:

    Sub MoveColumn()
        Dim lst As ListObject
        Set lst = ActiveSheet.ListObjects(1)
        lst.ListColumns("Course_ID").Range.Cut
        lst.ListColumns("Student_ID").Range.Insert Shift:=xlShiftToRight
    End Sub

    This assumes that the table is the first one on the sheet. You can also use ListObjects("Table1"), where Table1 is the name of the table.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by run4it Wednesday, February 26, 2020 9:13 PM
    Wednesday, February 26, 2020 8:30 PM

All replies

  • Try this:

    Sub MoveColumn()
        Dim lst As ListObject
        Set lst = ActiveSheet.ListObjects(1)
        lst.ListColumns("Course_ID").Range.Cut
        lst.ListColumns("Student_ID").Range.Insert Shift:=xlShiftToRight
    End Sub

    This assumes that the table is the first one on the sheet. You can also use ListObjects("Table1"), where Table1 is the name of the table.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by run4it Wednesday, February 26, 2020 9:13 PM
    Wednesday, February 26, 2020 8:30 PM
  • Thank you!  It also occurred to me that I could move the column before I formatted it as a column. 

    There are no mistakes; every result tells you something of value about what you are trying to accomplish.

    Wednesday, February 26, 2020 9:13 PM