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

BPW

New Member
Joined
Mar 7, 2013
Messages
31
Office Version
  1. 365
Platform
  1. Windows
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.

1629400890874.png


Thanks in advance!
B
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
this should do it

VBA Code:
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
 
  • Like
Reactions: BPW
Upvote 0
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

VBA Code:
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
 
  • Like
Reactions: BPW
Upvote 0
Solution

Forum statistics

Threads
1,223,250
Messages
6,171,036
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top