Union range that fall below one another - VBA Excel -

i trying union 2 ranges fall below 1 perform multiple regression.

this example make question clear..

say read in range a1:a5 , a:6 a:10 parameters in function. want create 5x2 matrix these 2 ranges next 1 , assign variable.

i require data in format can regress on variable containing range b1:b5.

resorting data in sheets not ideal need number of times , rolling down data each time.

any appreciated, kind regards.

this code far:

function dimson1yearly(y_range, x_range1, x_range2, x_range3)  dim entirerange variant dim integer  entirerange = application.union(x_range1, x_range2, x_range3)   dim regressionstats variant regressionstats = worksheetfunction.linest(y_range, entirerange, true, true)  dim j integer dim sum double sum = 0  j = 1 3     sum = sum + regressionstats(1, j) next  dimson1yearly = sum  end function 

note: works fine when x ranges chosen next each other not when below - i.e. same column

you need use set

set entirerange = application.union(x_range1, x_range2, x_range3) 

with object variables.

you should use dim entirerange range if know range, make easier spot problems. however, if using function in worksheet, need use breakpoint discover this. little tricky. best way view immediate window (ctrl-g) , try execute function there using:

?dimson1yearly(range("a1"),range("b4"),range("d6"),range("e7")) 

if have used dim entirerange range receive error "object variable or block variable not set".