range name each array(or column) .lt us assume A1 is having a haeding and the data starts from A2 down
name the range as for e.g. array1(1 is useful because you can increment it in your macro ).
in the refers to window type
similarly array2 for column B data etc.
these are called dynamic range names and if you add or reduce the rows the range name will refer to expanded column
These naming ranges you do in the macro itself
for columns colatality create a variable in the macro e.g. j for the number of the last column e,.g
dim j as integer
so you can loop the correlations 1 to j
ultimately the correlation in vba will be something like this
will give the correlation between column A(1) and column B(2)
you have to have loops.
these are some of the ideas you can work on.