Hide Columns hides but then reappears

[rplohocky]

Hello,
I have a strange one for ya. I highlight the columns that I want to hide then right click and select Hide but when i click on ANY cell in the sheet they reappear. I've tried 1 column, 2, 3 and more, same result. I have 5 sheets in this workbook and they are all the same. It has macros in it and I have been able to hide in the past so this is new.

 

[skillilea]

Did you comment out your worksheet events and see if it still happens?

Private Sub Worksheet_Change(ByVal Target As Range)
exit sub

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
exit sub

End Sub

 

[rplohocky]

I have a macro that autofits columns so i don't have to do this every time the number is to long for the cell. I just commented it out and now it will hide. So am I to assume that i only get one of those options?

 

[skillilea]

Is it still hiding columns after you commented the macro out or is it fixed?

 

[rplohocky]

This has been fixed by commenting a macro out but I still dont know if there is another solution to be able to use the macro and still have hidden columns.

 

[skillilea]

Can you post your macro?

 

[rplohocky]

'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Cells.EntireColumn.AutoFit
'End Sub

 

[jolivanes]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Long
Cells.EntireColumn.AutoFit    '<----- Could be a bad habit to get used to.
    For i = 36 To 3 Step -1    '<---- Change as required
        Columns(i).EntireColumn.Hidden = True
    Next i
End Sub

 

[zero269]

I highlight the columns that I want to hide then right click and select Hide but when i click on ANY cell in the sheet they reappear.

Makes sense due to your Worksheet_SelectionChange.
Replace your 'Cells.EntireColumn.AutoFit' with the ranges you want.
You may even want to use Worksheet_Change instead of the Worksheet_SelectionChange.
I noticed you commented the entire thing out, which means you shouldn't be having that issue any more...

So am I to assume that i only get one of those options?

Correct. Hidden Columns can be resized, so using AutoFit against your entire Sheet will not exlude hidden columns.

However, if you stil need the AutoFit functionality, you can specify ranges around any columns you want to remain hidden. I have a sheet I use to generate URLs and is only used for web scraping... so I don't need to see it.

Here's two examples you may find useful:
One uses a single range of columns, while the other uses more than one range.

' Single Range
Sub AutoFitSome1()

    ActiveSheet.Columns("A:C").AutoFit ' Using Active Sheet

End Sub

In the following example, I have column 'D' Hidden, but want the other columns within the Table to AutoFit:
You need only add the ranges within your dataset, not beyond it.

' Multiple Ranges
Sub AutoFitSome2()

    With ActiveSheet
        .Columns("A:C").AutoFit
        .Columns("E:F").AutoFit
    End With
  
End Sub

 

[Peter_SSs]

So am I to assume that i only get one of those options?

You could try something like below or it might be able to be tailored better if your existing code that autofits has some sort of range selection in it.
Any columns that are hidden should remain hidden and visible columns should AutoFit.

Sub AutoFitVisibleColumnsOnly()
  Dim c As Range
 
  Application.ScreenUpdating = False
  For Each c In ActiveSheet.UsedRange.EntireColumn.Columns
    If Not c.Hidden Then c.AutoFit
  Next c
  Application.ScreenUpdating = True
End Sub