Aug 22 2022 03:17 PM
This might be a basic question...
I want to record a macro using Excel on mac ver 16.64. The calculations are simple. E.g.: the average of column C. The challenge is that on some data files i have 100 rows of data and on others 500+ rows. How can i make the same macro work for all cases...100 rows for 1000 rows and everything in between.
Thanks in advance
Aug 22 2022 03:20 PM
The AVERAGE function ignores empty cells, so you could use the largest range you expect.
If you want to use the exact range we'd have to know in detail what you want to do.
Aug 22 2022 03:33 PM
Aug 23 2022 12:23 AM
Please provide some details.
Aug 31 2022 08:42 AM
See attached sample file. Rows 1 has variable names, rows 2-84 have data.
I begin analysis in cell BC100..you can see the formula in it. Then move down to cells BC101, BC 103 and so on and enter the formulae. I copy the formulas in BC100-BC106...to adjacent columns till col DV. Then i transpose the results matrix and sort (as you will see in the sheet).
I can record a macro to do all the above but i want the macro to adjust so that it does the calculations with all rows of data. In this case data is in rows 2-84. In the next case, data may be in rows 2-156. How do i make the macro adjust and use all available rows of data?
Thank you for your time
Aug 31 2022 02:25 PM
The attached version contains a formula solution.
Sep 01 2022 06:52 AM
Sep 01 2022 07:18 AM
This is what I see. But the formulas in column A will only work in Microsoft 365 and Office 2021, not in older versions.
Here is a version that I hope will work in older versions.
Sep 01 2022 08:11 AM