Using a macro to run many Univariate Regressions over ranges that contain non numeric data

[csmith-han]

I searched this forum and found this code to run 60 simple regressions for a range of data in column 3 against the range data in column 65, then 4 against 66, then 5 against 67, and so on. What I would like is however to modify the code for a range of data that contains non numeric data (i.e. blank but not empty cells like ""). Unfortunately, I can't seem to correctly modify the code in order to make that happen. Can anyone help me please? Thanking you in anticipation.

Please Login or Register  to view this content.

[kalak]

The "ATPVBAEN.XLAM!Regress" regressions will only run on numeric data.

If you want to use these you should first delete any rows containing blanks or other non-numeric characters, apart from the header row if any.

'Deleting' doesn't mean you have to modify the data permanently. Only for the purpose of running the regression. You can easily reinstate your data to its original after running the regression.

Alternatively you can use custom regressions that will skip any row where non-numeric data occurs.

[csmith-han]

Thanks Kalak. I remedied this by running a macro to empty and delete blank cells and shift cells up within column prior to running the regression macro, and it had worked a treat.