+ Reply to Thread
Results 1 to 2 of 2

Subtotal/Grouping in VBA

  1. #1
    Registered User
    Join Date
    06-06-2016
    Location
    Lancaster, PA
    MS-Off Ver
    2010
    Posts
    2

    Subtotal/Grouping in VBA

    Hey guys,

    Got hit with a large excel project at work (they saw I had Excel certification, but this is even past my knowledge) and found an answer to an earlier question here, so I signed up. Now, however, I have a new hurdle that I can't seem to figure out or find any information on that fits my application. My VBA knowledge is limited, so bear with me.

    I currently have a macro created that is bringing raw data in from a back end source and running some formulas on it to get data we can use. My issue is that every day this data is coming in, it is a different amount. One day there might be 5 lines of data, and 200 the next. Once I have the data in and analyzed, which I have a macro successfully doing no matter how much data comes in, I need to subtotal how many lines came in, and then group them so they can be minimized and expanded when need be. Also, this is updated each day, so every time the macro is run it will be one line lower, because it is a new day. So everything needs to be completely relative.

    I have the current VBA code of what needs done copied below, but that is for one example of one day of data. I need this process to work no matter how many lines of data come in. So my bottom line question is: Is there a way to make the subtotaling and grouping relative to how many lines of data come in?

    Thanks a ton for the help, this is a past my excel knowledge but I need to get the project done for work.

    Range("L38320").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(3,R[1]C:R[16]C)"
    Range("A38321:L38336").Select
    Selection.Rows.Group
    Range("F38340").Select

  2. #2
    Registered User
    Join Date
    06-06-2016
    Location
    Lancaster, PA
    MS-Off Ver
    2010
    Posts
    2

    Re: Subtotal/Grouping in VBA

    Quick update: The macro that I have copied above will run properly as long as it starts on the same line and there are the same number of lines or less than what was recorded. However, if there are more lines of data and/or I bump down one row (simulating a new day) the macro does not work anymore. Thanks again!

+ 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. Subtotal or grouping of values in columns (not rows)
    By Manjula1 in forum Excel General
    Replies: 1
    Last Post: 09-15-2015, 05:00 AM
  2. Macro for grouping/subtotal and row fill by column text value
    By toph423 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-31-2015, 06:17 AM
  3. A Possible Subtotal, Grouping or Filtering Question
    By rmac23 in forum Excel General
    Replies: 2
    Last Post: 04-16-2014, 05:33 PM
  4. Replies: 0
    Last Post: 09-25-2012, 03:04 PM
  5. fill in blank cells after Grouping Subtotal
    By sj2487 in forum Excel General
    Replies: 8
    Last Post: 08-31-2009, 06:06 PM
  6. grouping and subtotal by month
    By bix in forum Excel General
    Replies: 1
    Last Post: 07-11-2006, 07:33 AM
  7. [SOLVED] Excel 2003 Subtotal order and grouping incorrect in SP1
    By Adam Platt in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-09-2005, 08:06 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