Group / Ungroup columns VBA code

[blackorchids2002]

Hi,

I badly needing your help coz most of you here are already an expert in writing vba codes.

I have an excel file where I need to show 13 months rolling in my report. Then, have written a code where originally it will just hide or unhide columns depending on the reporting month I selected in the control sheet. So let say, if I would choose November month end, in the "Report" sheet, it will display the data from November 2010 to November 2011. The rest of the months are hidden. I don't have a problem with that part. Now, I would also want to apply the same procedure in grouping or ungrouping columns.

I've got an error in my code if I have to select previous months when it comes to group/ungroup columns.



Sub Hide_UnHide_Col()

Sheets("Report").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+1"
Range("B2").Select
Selection.Copy
Range("C2:AB2").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Columns("C:AB").Select ' this is the range of the columns
If Selection.Columns.Group = False Or Selection.Columns.Ungroup = isnothing Then
End If
Selection.Columns.Ungroup
Selection.EntireColumn.Hidden = False
Range("D6").Select

hcol = 16 + Sheets("List").Range("H1")
lcol = hcol - 14

ActiveSheet.Range(Cells(2, 3), Cells(2, lcol)).Select ' this is to hide/unhide columns
Selection.Columns.Group
Selection.EntireColumn.Hidden = True
ActiveSheet.Range(Cells(2, hcol), Cells(2, 28)).Select ' this is to hide/unhide columns
Selection.Columns.Group
Selection.EntireColumn.Hidden = True

Rows("2:2").Select
Selection.ClearContents
Range("D6").Select
Sheets("Control").Select
Range("C4").Select
End Sub


I would appreciate your help on this..

Thanks,
Blackorchids

 

[adam087]

Where does it throw the error?

 

[blackorchids2002]

Where does it throw the error?

Hi Adam,

This is the section where I got an error, see below code. Originally in the 'Report" sheet there is no column that is grouped yet.

The idea is, whatever months I selected it should group/ungroup accordingly and not repetitive grouping.

Columns("C:AB").Select ' this is the range of the columns
If Selection.Columns.Group = False Or Selection.Columns.Ungroup = isnothing Then
End If
Selection.Columns.Ungroup


I can send you the file if you want to see it. How should I attach it here?

 

[adam087]

I'm not sure exactly what this is trying to achive. Becuase you end the If statement without doing anything then it doesn't really add any value. You might try

With Columns("C:AB")
  .ClearOutline
End With

That will remove all current grouping.

If you want it to do more let me know.

Regards
Adam

 

[blackorchids2002]

I'm not sure exactly what this is trying to achive. Becuase you end the If statement without doing anything then it doesn't really add any value. You might try

With Columns("C:AB")
  .ClearOutline
End With

That will remove all current grouping.

If you want it to do more let me know.

Regards
Adam

Hi Adam,

Thanks a lot with your help. It really works with my codes below.


Sub Hide_UnHide_Col()

Sheets("Report").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+1"
Range("B2").Select
Selection.Copy
Range("C2:AB2").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Columns("C:AB").Select ' this is the range of the columns
Selection.Columns.ClearOutline 'to clear all column groupings
Selection.EntireColumn.Hidden = False
Range("D6").Select

hcol = 16 + Sheets("List").Range("H1")
lcol = hcol - 14

ActiveSheet.Range(Cells(2, 3), Cells(2, lcol)).Select ' this is to hide/unhide columns
Selection.Columns.Group
Selection.EntireColumn.Hidden = True
ActiveSheet.Range(Cells(2, hcol), Cells(2, 28)).Select ' this is to hide/unhide columns
Selection.Columns.Group
Selection.EntireColumn.Hidden = True

Rows("2:2").Select
Selection.ClearContents
Range("D6").Select
Sheets("Control").Select
Range("C4").Select
End Sub

Happy New Year to you and your family.
Thanks,
Lhe (a.k.a "blackorchids2002")

 

[adam087]

No problem Lhe.

Your code can be tidied up a lot if you're planning on using it regularly, or doing much more with it. Everywhere you are selecting cells then doing something with them. There is no need. In all cases (for example)

Rows("2:2").Select
Selection.ClearContents
Range("D6").Select
Sheets("Control").Select
Range("C4").Select

Can become...

Rows("2:2").ClearContents

As there is a lot of unnecessary 'selecting.'

Similarly

Sheets("Report").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+1"

Can become

With Sheets("Report")
    .Range("A2") = "1"
    .Range("B2").FormulaR1C1 = "=RC[-1]+1" 
End With

Regards
Adam