Trouble with RSq function in VBA

First let me describe what I'm TRYING to do. I have a large dataset of test results that is Autofiltered to sort by 20 or so different columns. I also have a number of charts that conveniently show only the test results that are visible in the Autofilter. I guess this is the default behavior of charts?

I am trying to put together a summary of performance predictors that are compared to each other by their linear regression R-squared value. The RSq function does this just fine, but it does not ignore the hidden rows when Autofiltered. So I'm writing some code to do this using the "SpecialCells(xlCellTypeVisible)" method and running into problems.

When I create ranges like this:

Please Login or Register  to view this content.

It works fine with the excel function Sum, and sums only the visible filtered cells. When I use this same format on 2 ranges with RSq, I get a 1004 error. When I use the range above without the SpecialCells property, RSq works just fine. Why doesn't RSq like using the SpecialCells property? I need help finding a work-around.

One thought I had that probably isn't possible - can VBA read the R-squared value off of a chart with a best fit curve on it? Since my charts only show the filtered data this would give me what I want.

Ok, so I'm scrapping that approach and trying to manually calculate R squared by using For/Next loop that tests each row to see if it is visible or hidden with the Autofilter.

I'm running into an issue with setting up a range variable. What am I doing wrong?

Please Login or Register  to view this content.

This gives a 1004 error.