facing problems hiding columns in excel VBA - copy and insert columns-CodePudding

in my worksheet i use the following VBA code (below), to copy the red bordered cells(blue) and insert them into the green bordered cells.

Now i would like to add a function to hide column D and E. The problem is, if I hide the two columns and copy/insert new columns with my VBA Button, they are also hided.

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

How can i reach the goal? Thanks!!

This is my vba code:

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

CodePudding user response：

you can simply add 1 line...

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

CodePudding user response：

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
        With .Cells(LastColumnRow, 1).End(xlToRight)
            .Offset(1 - LastColumnRow, 1).Insert xlShiftToRight
            .Resize(, cCount).EntireColumn.Hidden = False
        End With
        .EntireColumn.Hidden = True
    End With
    
End Sub

CodePudding user response：

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