Grouping Columns via Macro

[Wheelie686]

I'm trying to hide data that occurs in the past. If I make a sheet from scratch it seems to work but the report I'm trying to adapt this to, seems to just stall and eventually crash Excel.
In my "WorkingReport" attachment, I have how my report is formatted (minus some sensitive data). This is the document that stalls when running essentially the same macro.
In my "Report" attachment, it works fine but it seems I have to strip a lot of the formatting & empty rows/columns away to make it work. This makes it very hard to look at and bland.

Any help would be appreciated.

FYI - Sorry I mislabelled my attachments. You would think the one named "WorkingReport" would work but that's the one that's broken

[Keebellah]

Hi, first of all what are you trying to do the Report.xlsm is different from the Working Report.xlsm.
The first columns are different.
And what is it exactly that you are trying to achieve?
Your macro runs but ... I don't see what it does and the 'broken' report you hide the forst columns so that will not work.
You must start with the correct column.
Without a clear idea of what YOU are trying I cannot build on it.

[AlphaFrog]

In WorkingReport.xlsm, the dates are in row 5. Your macro was looking for them in row 1.

Change this...
Set SrchRng = Range("1:1").SpecialCells(xlCellTypeConstants)

To this...
Set SrchRng = Range("5:5").SpecialCells(xlCellTypeConstants)

[Wheelie686]

Alpha Frog - I can't believe I miscounted... thanks for the help. That somewhat worked. It's now created multiple levels of grouping. See "Working Report V2" attached. I'm trying to group everything from the oldest date to 'last Friday'grouped and leave everything else alone. So that would mean everything from April 8 (oldest date) to June 21 (previous Thursday) grouped and everything else stays as is. I also want Column A left alone since it has data that I want shown at all times and no dates in it.

Keebalah - Sorry if I made it confusing. Disregard the "Report" document. What I want is code to group everything from column B to whatever column shows the first day of current week and group them so I can easily put them aside but have them retrievable if I want to look at old data. I want to keep column A visible at all times.

[AlphaFrog]

everything from April 8 (oldest date) to June 21 (previous Thursday) grouped and everything else stays as is.

Sub HideOld2()
    
    Dim PreviousThursday As Date, rngEnd As Range
    
    PreviousThursday = Date - Weekday(Date, 5) + 1
    Set rngEnd = Rows(5).Find(PreviousThursday, , xlFormulas, xlWhole, 1, 2, 0)
    If Not rngEnd Is Nothing Then
        Range("B5", rngEnd).Group
        ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
        Range("A1").Select
    Else
        MsgBox Format(PreviousThursday, "dddd mmm d, yyyy"), vbExclamation, "Previous Thursday Not Found"
    End If
    
End Sub

[Wheelie686]

That works out great! Is there a way to modify it to skip anything in Row 5 that isn't a date? I'm modifying it for a different sheet and the layout is a bit different. For example B5 to H5 would be June 24th to June 30th, then I5 would be something like "Total of this week", then it would start again with J5 to P5 being July 1st to July 7th and so on and so forth with that type of rotation in columns.

[AlphaFrog]

You're welcome.

Are the "not date" cells blank, text, or numbers?

You can only group contiguous cells. So you would have multiple groups

It would be best to post an example workbook with a duplicate sheet that showed the result you want.

[Wheelie686]

Here's a copy of the bare bones report that I'm now trying to adapt.
Row 10 is what I'm trying to query so that it can tell me what's older than "Previous Friday" - I've adapted your code a little, making it a Worksheet Activate for Sheet 5 as well as checking if there are already grouped weeks - if so, it seems to group ontop of groupings and eventually I'll end up with 52 separate groupings ontop of each other... or so I assume the way it's behaving That would look awful!
As you can see Column K, S, AA, etc aren't going to end up being Date columns but rather formulas to sum the data for the 7 day period. Also if you skip ahead to Column CE, you'll notice it now has CE/CF, CN/CO, CW/CX, etc... that are blank in pairs. There was a reason I added a completely blank column but that's another story
I am still trying to accomplish the same thing you helped me with before, only now the aforementioned columns don't contain dates in Row 10.

[AlphaFrog]

if so, it seems to group ontop of groupings and eventually I'll end up with 52 separate groupings ontop of each other... or so I assume the way it's behaving That would look awful!

Yes. As I mentioned before, a single group is made up of contiguous cells.

Why did you make it an Worksheet_Activate event procedure?

This will hide old columns with dates (formulas in row 10) without using grouping.

Sub Hide_Old3()
    Dim PreviousFriday As Date, rngEnd As Range
    Application.ScreenUpdating = False
    Columns.Hidden = False
    PreviousFriday = Date - Weekday(Date, 6) + 1
    Set rngEnd = Rows(10).Find(Format(PreviousFriday, Range("D10").NumberFormat), , xlValues, xlWhole, 1, 2, 0)
    If Not rngEnd Is Nothing Then
        Columns("D").Hidden = True
        Range("D10", rngEnd).SpecialCells(xlCellTypeFormulas).EntireColumn.Hidden = True
        Range("A1").Select
    Else
        MsgBox Format(PreviousFriday, "dddd mmm d, yyyy"), vbExclamation, "Previous Friday Not Found"
    End If
    Application.ScreenUpdating = True
End Sub

[Wheelie686]

I made it a Worksheet_Activate event procedure so it would automatically group old data and put it to the side sort to speak if you want to look at only current data, yet readily available if you want to check data from several weeks ago or whatnot. So in short it seems the answer is no as your newest code still leaves the columns with "Totals" visible.

[AlphaFrog]

I don't know what you want to hide or show. You previously said you only want to hide dates. And you didn't provide a result sheet that showed what you want.

[Wheelie686]

I would like it to group everything older than this past Friday (June 22 in this case) starting at column D