Nascondere colonne con Excel vba

Question

Ritorno a chiedere il Vostro aiuto!

Ho necessità di semplificare un file excel, copiandolo in un nuovo file/foglio e nascondere alcune colonne.

Il codice seguente, frutto di esempi, guide etc non funziona e si ferma quando devo nascondere le colonne e successivamente aggiungere i bordi ai dati "finali". Mi potere aiutare per cortesia? Grazie per la Vostra attenzione.

Ciao

Giovanni

Option Explicit

Sub CopySheet()

On Error GoTo ErrHandler

Dim wbNew As Workbook

Dim wsActive As Worksheet

Dim wsNew As Worksheet

Dim varSaveName As Variant

    Set wsActive = ActiveSheet

    Set wbNew = Workbooks.Add(xlWBATWorksheet)    ' add new workbook with only one worksheet

    Set wsNew = wbNew.Worksheets(1)

    wsActive.UsedRange.Copy

    wsNew.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats

     Columns("E:BZ").Select

    Selection.EntireColumn.AutoFit

     With Selection

        .HorizontalAlignment = xlCenter

        .VerticalAlignment = xlBottom

     End With

'-->>> qui si ferma il codice, sia con una o altra riga

'Range("BD:BE; BB; AY; AV:AW; AS; A:AQ; AJ:AM; AD; V:AB; S; I:N; B:F; A").EntireColumn.Hidden = True

'  ActiveSheet.Range("BD:BE"; "BB"; "AY"; "AV:AW"; "AS"; "A:AQ"; "AJ:AM"; "AD"; "V:AB"; "S"; "I:N"; "B:F"; "A").EntireColumn.Hidden = True

ActiveSheet.Range("A3:BZ4").ClearContents

Call Format_Column

Call Add_Border

    Dim Name As String

    Name = Range("B6").Value & "_" & Range("G6").Value & "_" & Range("J6").Value

    varSaveName = Application.GetSaveAsFilename(InitialFileName:=Name, filefilter:="Excel Files (*.xlsx), *.xlsx", Title:="Save As")

    If TypeName(varSaveName) = "Boolean" Then

        MsgBox "Save Cancelled!"

    Else

        Application.DisplayAlerts = False

        wbNew.SaveAs varSaveName

        Application.DisplayAlerts = True

    End If

    Application.Goto wsNew.Range("A1")

ExitSub:

    Exit Sub

ErrHandler:

  If Err.Number <> 0 Then

        Dim mbr As VbMsgBoxResult

        mbr = MsgBox( _

            "Error #" & Err.Number & ": " & Err.Description & vbNewLine & _

            "Would you like to continue?", vbExclamation Or vbYesNo)

        If mbr = vbYes Then Resume ExitSub

        Application.Quit

    End If

   End Sub

'----------

Sub Add_Border()

Dim sht As Worksheet

Dim LastRow As Long

Dim LastColumn As Long

Dim StartCell As Range

Set sht = ActiveSheet ' Worksheets("Foglio1")

Set StartCell = Range("A5")

  LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row

  LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column

 sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select

    With Selection.Borders

            .LineStyle = xlContinuous

            .Weight = xlThin

            .ColorIndex = xlAutomatic

        End With

    Range("E4").Select

 End Sub

'----------

Sub Format_Column()

Application.ScreenUpdating = False

    Dim lngLstCol As Long, lngLstRow As Long

    lngLstRow = ActiveSheet.UsedRange.Rows.Count

    lngLstCol = ActiveSheet.UsedRange.Columns.Count

    With Range(Range("A5"), Cells(lngLstRow, 2)).Borders(xlEdgeLeft)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Range(Range("A5"), Cells(7, lngLstCol)).Borders(xlEdgeTop)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Range(Range("A5"), Cells(3, lngLstCol)).Borders(xlEdgeBottom)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

     Application.ScreenUpdating = True

ActiveSheet.Range("G5", _

   ActiveSheet.Range("G5").End(xlDown)).Select

   With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .ThemeColor = xlThemeColorDark2

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

ActiveSheet.Range("A5", _

   ActiveSheet.Range("A5").End(xlToRight)).Select

   With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .ThemeColor = xlThemeColorDark2

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

Application.ScreenUpdating = True

ActiveWindow.DisplayGridlines = False

End Sub

Answer 1

[...]

'-->>> qui si ferma il codice, sia con una o altra riga

'Range("BD:BE; BB; AY; AV:AW; AS; A:AQ; AJ:AM; AD; V:AB; S; I:N; B:F; A").EntireColumn.Hidden = True

'  ActiveSheet.Range("BD:BE"; "BB"; "AY"; "AV:AW"; "AS"; "A:AQ"; "AJ:AM"; "AD"; "V:AB"; "S"; "I:N"; "B:F"; "A").EntireColumn.Hidden = True

[...]

Ciao Giovanni,

Prova a sostituire l'istruzione problematica con:

ActiveSheet.Range("BD:BE,BB:BB,AV:AY,AS:AS,AQ:AR,AJ:AM,AD:AD,V:AB,S:S,I:N,B:F,A:A").EntireColumn.Hidden = True

===

Regards,

Norman

Answer 2

Grazie per il prezioso aiuto: Funziona.

Ciao Giovanni