CODEWITHSUNDEEP
excelvba
Caveatrob
Caveatrob

Reputation: 13267

Excel vba move columns and preserve widths

I have code to move columns left and right based on keypresses, but the column widths aren't preserved as I move the columns. I'd like the column I'm moving to retain its width, and the columns its moving through also preserve their widths.

Public Sub moveColumnleft()

    ActiveCell.EntireColumn.Select
    Selection.EntireColumn.Cut
    If ActiveCell.Column = 1 Then Exit Sub

    ActiveCell.offset(0, -1).Insert Shift:=xlLeft
    ActiveCell.offset(0, -1).Select


End Sub

Public Sub moveColumnRight()

    ActiveCell.EntireColumn.Select
    Selection.EntireColumn.Cut
    ActiveCell.offset(0, 2).Insert Shift:=xlRight
    ActiveCell.offset(0, 1).Select

End Sub

Upvotes: 0

Views: 4218

Answers (1)

MPękalski
MPękalski

Reputation: 7103

Storing the moved column witdth worked for me (Excel 2007). See code below

Public Sub moveColumnleft()
    ActiveCell.EntireColumn.Select
    Selection.EntireColumn.Cut
    '' store column width in variable **tmp**
    Dim tmp As Double 
    tmp = ActiveCell.EntireColumn.ColumnWidth
    If ActiveCell.Column = 1 Then Exit Sub

    ActiveCell.Offset(0, -1).Insert Shift:=xlLeft
    ActiveCell.Offset(0, -1).Select
    '' apply the stored width to the moved column
    Range(ActiveCell.Address).ColumnWidth = tmp
End Sub

Upvotes: 2

Related Questions