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

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!

I'm no VBA man, but I seem to remember something like " CurrentRegion.Select" in VBA. Does this help?

Let me go in and play with it. Thanks for the response!

You could also use Tables, but I have no idea how this works in VBA :confused2:

yeah that is my issue. I have a million ideas in my head that would work, but just don't know how to get it into VBA language!

I do, but 'no file, no code'.

Would you mind elaborating on what you mean?

snb likes to work on real data . Post a sheet ( not a pic pse) with some (eventually mock-up) data and any code you already have.

I'm pretty sure he'll come up with something )

Sorry, still a little confused here (again, not this much excel knowledge and new to the forum). I can't post the real sheet because it is sensitive data that can't be out on the internet. I attempted to create a mockup in a "fake" workbook, but there is no way for me to add an excel attachment to a reply. Thank you guys for the help!

If this helps at all, it is the entire code of what needs done. But as I originally posted, this is for one example. I need it to do the same subtotal and grouping it does in this code, but for any amount of data, whether it be more or less. Thank you!

Sub excel_help()
'
' excel_help Macro
'

'
ActiveCell.Offset(-24, 5).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(3,R[1]C:R[16]C)"
ActiveCell.Offset(1, -11).Range("A1:L16").Select
Selection.Rows.Group
End Sub