Grouping Columns based on content

[scotthannaford1973]

Hi all

okay - hoping you can help with some VBA here...

at the top of 52 columns I have the date of the

first day in each week - as per attached screenshot; I want to group all past date columns together and all future date columns. Obviously I can do this manually, but I'd like to automate it so that, ideally:

- all columns more than 3 weeks in the past (i.e. <today()-21)
- all columns more than 6 weeks in the future (i.e. >Today()+42)

are grouped (2 groups, not one, obviously)

I am sure this could be done through some nice coding, but I wouldn't know where to start!

much appreciated!
if image doesn't appear: https://ibb.co/h5SCvU</today()-21)

 

[Peter_SSs]

See if this is the sort of thing you are after. Test in a copy of your workbook.

The red numbers might need a little tweaking to get exactly what you want.

Sub Group_Weeks()
  Dim CurrCol As Long
  
  ActiveSheet.UsedRange.EntireColumn.ClearOutline
  CurrCol = Application.Match(CLng(Date), Rows(1))
  If CurrCol > 3 Then Columns(1).Resize(, CurrCol - 3).Group
  If CurrCol < 46 Then Columns(CurrCol + 6).Resize(, 53 - CurrCol - 6).Group
End Sub

 

[scotthannaford1973]

See if this is the sort of thing you are after. Test in a copy of your workbook.

The red numbers might need a little tweaking to get exactly what you want.

Sub Group_Weeks()
  Dim CurrCol As Long
  
  ActiveSheet.UsedRange.EntireColumn.ClearOutline
  CurrCol = Application.Match(CLng(Date), Rows(1))
  If CurrCol > 3 Then Columns(1).Resize(, CurrCol - 3).Group
  If CurrCol < 46 Then Columns(CurrCol + 6).Resize(, 53 - CurrCol - 6).Group
End Sub

Fabulous - thanks, Peter - that works well; just need to work out now how to apply it to certain columns (say from G onwards) rather than to every column in the sheet. Much appreciated!

 

[Peter_SSs]

Fabulous - thanks, Peter - that works well; just need to work out now how to apply it to certain columns (say from G onwards) rather than to every column in the sheet. Much appreciated!

You're welcome. Post back with more details if you can't make the modification required.

 

[scotthannaford1973]

You're welcome. Post back with more details if you can't make the modification required.

for anyone who spots Peter's great solution, if your column heading isn't in row 1, you can change the number in brackets to group by content of the correct row.

 

[Peter_SSs]

for anyone who spots Peter's great solution, if your column heading isn't in row 1, you can change the number in brackets to group by content of the correct row.

To clarify, since there is more than one, that would be the red number in brackets, not the blue number in brackets.

Sub Group_Weeks()
  Dim CurrCol As Long
  
  ActiveSheet.UsedRange.EntireColumn.ClearOutline
  CurrCol = Application.Match(CLng(Date), Rows(1))
  If CurrCol > 3 Then Columns(1).Resize(, CurrCol - 3).Group
  If CurrCol < 46 Then Columns(CurrCol + 6).Resize(, 53 - CurrCol - 6).Group
End Sub