Grouping in VB

[davidhall80]

I have two Total columns. Because this is moving data, I never know which two columns the total columns are in. I need to group the data between the columns. The first column heading is "YTD" and the second column heading is "Monthly". The column headings begin in row 10.

 

[Damon Ostrander]

Hi david,

You mentioned that the column headings "start in row 10", implying that they might end in a different row. Assuming that you meant "are in row 10" and you want the YTD and Monthly columns to be included in the group, the following code should do it:

Sub GroupTotalColumns()
   'Groups columns starting with YTD and ending with Monthly in row 10
   Dim iCol    As Integer
   Dim iCol1   As Integer
   Dim iCol2   As Integer
   iCol1 = 0
   
   For iCol = 1 To Cells(10, 256).End(xlToLeft).Column
      If iCol1 = 0 Then
         If Cells(10, iCol).Value = "YTD" Then iCol1 = iCol
      Else
         If Cells(10, iCol).Value = "Monthly" Then
            iCol2 = iCol
            GoTo FoundGroup
         End If
      End If
   Next iCol
   MsgBox IIf(iCol1 = 0, "YTD", "monthly") & " header not found.", vbExclamation, "Can't group columns"
   Exit Sub
   
FoundGroup:
   Range(Columns(iCol1), Columns(iCol2)).Group
   
End Sub

Keep Excelling.

Damon

 

[davidhall80]

Thanks !!!!!