+ Reply to Thread
Results 1 to 12 of 12

Grouping Columns via Macro

  1. #1
    Forum Contributor
    Join Date
    05-27-2012
    Location
    New Brunswick, Canada
    MS-Off Ver
    Excel 2016
    Posts
    349

    Grouping Columns via Macro

    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
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,937

    Re: Grouping Columns via Macro

    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.
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Grouping Columns via Macro

    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)
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Forum Contributor
    Join Date
    05-27-2012
    Location
    New Brunswick, Canada
    MS-Off Ver
    Excel 2016
    Posts
    349

    Re: Grouping Columns via Macro

    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.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Grouping Columns via Macro

    Quote Originally Posted by Wheelie686 View Post
    everything from April 8 (oldest date) to June 21 (previous Thursday) grouped and everything else stays as is.
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    05-27-2012
    Location
    New Brunswick, Canada
    MS-Off Ver
    Excel 2016
    Posts
    349

    Re: Grouping Columns via Macro

    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.

  7. #7
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Grouping Columns via Macro

    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.
    Last edited by AlphaFrog; 06-24-2018 at 05:49 PM.

  8. #8
    Forum Contributor
    Join Date
    05-27-2012
    Location
    New Brunswick, Canada
    MS-Off Ver
    Excel 2016
    Posts
    349

    Re: Grouping Columns via Macro

    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.
    Attached Files Attached Files
    Last edited by Wheelie686; 06-24-2018 at 09:03 PM.

  9. #9
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Grouping Columns via Macro

    Quote Originally Posted by Wheelie686 View Post
    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.

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    05-27-2012
    Location
    New Brunswick, Canada
    MS-Off Ver
    Excel 2016
    Posts
    349

    Re: Grouping Columns via Macro

    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.

  11. #11
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Grouping Columns via Macro

    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.

  12. #12
    Forum Contributor
    Join Date
    05-27-2012
    Location
    New Brunswick, Canada
    MS-Off Ver
    Excel 2016
    Posts
    349

    Re: Grouping Columns via Macro

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Grouping Columns using VBA
    By ola7mat in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-07-2016, 07:34 AM
  2. Help with grouping columns
    By MetroBOS in forum Excel General
    Replies: 3
    Last Post: 03-24-2016, 03:03 PM
  3. Grouping specified columns
    By martijnvanderveldt in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-26-2015, 10:57 AM
  4. Is there a possibility to create a macro for ungrouping and grouping columns?
    By sanip in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-06-2015, 02:29 AM
  5. [SOLVED] Grouping columns
    By rs1aj in forum Excel General
    Replies: 4
    Last Post: 08-04-2014, 12:27 PM
  6. [SOLVED] A macro for grouping of same values of several columns and totals
    By novice_excel_2012 in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 06-03-2012, 07:09 AM
  7. Replies: 0
    Last Post: 02-24-2011, 12:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1