Hide original columns after copying and inserting into new columns

user_12747764 0
Viewed 114

The following VBA code copies the red bordered cells (blue) and inserts them into the green bordered cells.

Sub ColumnCopyInsert()
    Range("D:E").Copy
  Range("D2").End(xlToRight).Offset(-1, 1).Insert xlToRight
End Sub

FUNCTIONALITY VBA

I would like to add a function to hide column D and E.

If I hide the two columns and copy/insert new columns with my VBA button, they are also hidden.

The goal is to hide D, E not the new columns.

vba excel
3 Answers

you can simply add 1 line...

Range("F:AA").EntireColumn.Hidden = False
user_88058420

Copy Contiguous Columns After Last Column

Option Explicit

Sub ColumnCopyInsert()
    
    Const ColumnsAddress As String = "D:E"
    Const LastColumnRow As Long = 2
    
    With Columns(ColumnsAddress)
        .EntireColumn.Hidden = False
        .Copy
        Dim cCount As Long: cCount = .Columns.Count
        .Cells(LastColumnRow, 1).End(xlToRight) _
            .Offset(1 - LastColumnRow, 1).Insert xlShiftToRight
        .EntireColumn.Hidden = True
    End With
    
End Sub
user_98140690 
Sub ColumnCopyInsert()

    Dim rng As Range, n As Long
    Set rng = Range("F1")

    ' find empty column
    Do While WorksheetFunction.CountA(rng.EntireColumn) > 0
        Set rng = rng.Offset(, 1)
    Loop
    
    With Range("D:E")
        n = .Columns.Count
        .Copy
        rng.Insert xlToRight
        rng.Offset(0, -n).Resize(, n).EntireColumn.Hidden = False
    End With

End Sub
user_127045930
Related