Quick VBA/Macro to group columns based on cell values in a row header

[BPW]

Hi Everyone,

I'm struggling to create an efficient macro to group columns based on values within a header row. Would someone be able help or point me in the right direction?

Below is a screenshot of what I'm trying to achieve. In essence I would like to group the columns by fiscal month (the columns that do not contain FY within the cell value. Please let me know if more information is needed.

Thanks in advance!
B

 

[Crystalyzer]

this should do it

Sub GroupCols()
    Dim i As Long, grpColStart As Long, grpColEnd As Long
    Dim strtCol As Long, endCol As Long
   
    Cells.Columns.Ungroup
   
    If Range("A6") = "" Then
        strtCol = Range("A6").End(xlToRight)
    Else
        strtCol = 1
    End If
   
    endCol = Cells(6, Columns.Count).End(xlToLeft).Column
       
    For i = strtCol To endCol
        If InStr(1, Cells(6, i), "FY") = 0 Then
            If grpColStart = 0 Then grpColStart = i
            grpColEnd = i
        Else
            If grpColStart <> 0 And grpColEnd <> 0 Then
                Range(Cells(6, grpColStart), Cells(6, grpColEnd)).Columns.Group
                grpColStart = 0
                grpColEnd = 0
            End If
        End If
       
    Next

End Sub

 

[Peter_SSs]

Assuming that the cells with "FY" in them are text cells and the date cells are actual dates (numbers) and are not the result of formulas, you could try

Sub MakeGroups()
  Dim rA As Range
  
  For Each rA In Range("E6", Range("E6").End(xlToRight)).SpecialCells(xlConstants, xlNumbers).Areas
    rA.EntireColumn.Group
  Next rA
End Sub