How to set up a variable range of rows for calculations in VBscript

Hi all. I'm a long time lurker but first time poster. Thanks for all the great content on this site!

I generate a report multiple times a day with varying data which is always formatted the same with exception to the number of "Results". I've created a macro to automatically resize columns, add columns, format, etc...but I'm stumped at how to perform a simple calculation of "Average", "Median", "Max", and "Min" on a range of values due to the fact that sometimes the range is performed on 10 rows, sometimes 8, sometimes 20, etc. I have highlighted the cells in the attached workbook with these simple calculations. I just need help identifying the code that will be flexible to allow for a varying number of Results every time I bring a report into my workbook. Thank you!!!

Originally Posted by Spennyd76

I'm stumped at how to perform a simple calculation of "Average", "Median", "Max", and "Min" on a range of values

Where/How do you want those results?

Shoot, my apologies. I attached my spreadsheet before saving it with some changes. I'm attaching here again. The cells highlighted in yellow contain the calculations.

Thank you!

Is this what you wanted?

Please Login or Register  to view this content.

Thank you! That worked perfectly!! I'm not sure what the syntax is doing, but it worked. Thanks!

It also got me thinking that other parts of my macro are bulky and too time consuming. In the sample spreadsheet I attached, is there a quick way of inserting a column between columns G and H but only for the table at the top? In other words, in the Sample spreadsheet, I'd insert a new column H from rows 14 to 25. But sometimes it would be rows 14 to 20, and other times 14 to 30. Right now, the way I'm doing it, it is simply inserting a new row for the whole spreadsheet and the macro is taking a long time to run. I'm wondering if it's because it's applying it to all 1,048,576 rows. ???

No code attached to .xlsx.

You can ask one question per thread.
You can continue if the new one is related to your original question, otherwise, please open a new thread.