Excel VBA: Why does EntireColumn.Hidden work only towards end of my code

[gertie193]

Hello,

I'm using Excel 2013 and modified some code to help me filter a certain column, 7th column (the hub name), copy that filtered data, paste that to a new workbook, save it by the hub name being filtered, and format the new workbook.

The code works. I'm just wondering why, however, I had to move the line of code below from the beginning of the format section of the code to near the end for it to work. I guess this is a theory question.

 Range("A:B").EntireColumn.Hidden = True

Here is the full code, if needed:

Thank you.

Sub FilterHubCopyToWkbk()
'partially used https://www.pcreview.co.uk/threads/re-vba-question-canceling-a-save-as-command-from-a-user-form.976722/


    Dim rng As Range
    Dim LR As Long
    Dim inval As Variant
    Dim filesavename As Variant
        
    'LR is counting last row
    LR = Cells(Rows.Count, "H").End(xlUp).Row
    Set rng = Range("A3:AQ" & LR)
    inval = InputBox("Enter hub to create report.")
    
    If inval = vbNullString Then Exit Sub
        
        With rng
            .AutoFilter
            .AutoFilter field:=7, Criteria1:=inval
            .SpecialCells(xlCellTypeVisible).Copy
            Workbooks.Add
            ActiveWorkbook.ActiveSheet.Paste
            
            filesavename = Application.GetSaveAsFilename(InitialFileName:=inval & " 2017 Ticketing Schedule", _
                                                                            Filefilter:="Excel workbooks (*.xlsx), *xlsx, (*.xlsm), *.xlsm")
                                                              
            If Not filesavename = False Then
                'CStr converts different types of expressions (Integer, Boolean and Date) to a string
                ActiveWorkbook.SaveAs Filename:=CStr(filesavename)

                'FORMATTING SECTION----------------------

               [B] 'HERE IS WHERE I HAD HIDDEN COLUMNS BUT IT DID NOT WORK[/B]


                Range("A1").CurrentRegion.RowHeight = 15
                Columns.AutoFit
                Range("AD1").ColumnWidth = 50
                Range("AE1").ColumnWidth = 11.3
                Range("AJ1").ColumnWidth = 19.43
                Range("A1").AutoFilter


               [B] 'HERE HIDDEN COLUMNS WORKS[/B]
                Range("A:B").EntireColumn.Hidden = True
                
                ActiveSheet.Name = inval
                ActiveWindow.Zoom = 90
                Range("c1").Select
            Else
                'close False workbook added/created of filtered hub
                ActiveWorkbook.Close False
                Application.CutCopyMode = False
                ActiveSheet.ShowAllData
            End If
            
        End With


End Sub

 

[Joe4]

Because you had this line after it:

Columns.AutoFit

That tells Excel to make every column wide enough to display its content.
So that will unhide hidden columns.

 

[gertie193]

Thank you so much! I never knew that.

Thanks!