Assign range to array

Hi and thanks in advance,

I found some code to compare the values in two spreadsheets by dumping everything into two arrays and outputing any differences.

I have a form where the user can specify the range to compare, by selecting a start column and start row and and end column and end row.

Code works fine when assigning more than one cell to the arrays eg. (strRangeToCheck = "A1:Z5000") but not if assigning a single cell eg. (strRangeToCheck = "A1:A1") OR (strRangeToCheck = "A1"). Crashes on line 4 of code below with Type Mismatch error and when I debug it says the two arrays are empty ...

Please Login or Register  to view this content.

Also, I occasionally get an Out of Memory error when assigning values to the arrays which is most likely due to them being variant arrays ... any suggestions on how to avoid/minimize this error?

Thanks
Deutz

There are various ways to check if a variant is an array eg:

Please Login or Register  to view this content.

Thanks Firefly2012,

That was exactly the problem. The variable only becomes an array when you assign a range which contains more than one cell. My code for testing LBound and UBound obviously does not work on a single value variable.

I couldn't see the forest for the array.

I'd still welcome some suggestions on the Out of Memory error.

Thanks
Deutz