Hide Merged Columns

[Takes2ToTango]

Hi All,

I have tried different codes but nothing seems to work.

I am after some code that will know where the merged cells end and then hide the columns it appears but leaves the first column

For example:

I double click The merged cell 'Test Merge'. It then know that the range A1:I1 is the merged cell range and proceeds to hide all columns besides A1.

This may be impossible but if anyone could provide any insight or alternatives, i would be very greatful

 

[Peter_SSs]

.. and proceeds to hide all columns besides A1.

.. what proceeds to hide all columns besides A1?
.. and do you mean all columns besides column A?

 

[arthurbr]

I don't really understand. Have you tried "center across selection" instead of merged cells?

 

[Takes2ToTango]

.. what proceeds to hide all columns besides A1?
.. and do you mean all columns besides column A?

Hi,

So it would be a double click event which then hides the correct columns. The end result would look like this.

Column A remains visible but the rest are hidden until you right click and unhide the columns

 

[Takes2ToTango]

I don't really understand. Have you tried "center across selection" instead of merged cells?

I have given it a go, however I would still need to hide the selected columns.
If they were a fixed range it would be an easy fix but users could add or remove columns within the range,

 

[NoSparks]

For a double-click inside that merged area, try this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Cells(1, 1).MergeArea) Is Nothing Then
        Cancel = True
        Range(Cells(1, 2), Cells(1, Cells(1, 1).MergeArea.Columns.Count)).EntireColumn.Hidden = True
    End If
End Sub

 

[Takes2ToTango]

For a double-click inside that merged area, try this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Cells(1, 1).MergeArea) Is Nothing Then
        Cancel = True
        Range(Cells(1, 2), Cells(1, Cells(1, 1).MergeArea.Columns.Count)).EntireColumn.Hidden = True
    End If
End Sub

That works perfectly,

Many thanks for your help!

 

[Peter_SSs]

Just another thought, depending on why you are doing this and how you would use it.
You could Group (on the Data ribbon tab) columns B:I so that you had the Collapse/Expand options at the top of the worksheet like below. Then instead of using double-click, you could just click the 1/2 buttons at the left or the -/+ buttons above to collapse and expend the columns.