I have a system written in Excel which has one addin, a parameters book and a data entry book. The addin constantly references the various sheets and cells on the parameters book to store and retrieve values.
One of these value lookup is referencing a specific sheet were there is a table that, starting from A1, that has 3 columns and about 10 rows - all contiguous.
When I perform the lookup on that sheet I use the following code to get the all the cells of the table.
Set rngTypeCol = wksLookupSheet.Range("A1").CurrentRegion.
This has been working fine for months and even today for 50% of the time it works correctly, returning all rows and cols, but sometimes it will simply return 'rngTypeCol' as a single cell 'A1'
I have never encountered 'CurrentRegion' failing before - unless used on a protected sheet (which this definitely isn't - I have checked)
The code goes onto search for a specific text value - which I know is present - and so I can have the code stop when it is searching for that value. Whilst stopped in debug mode I have queried 'A2' and 'B1' and both have a text value and yet CurrentRegion still only returns 'A1'.
Unfortunately I can't attach the code as it is far to vast and complex and I am unable to replicate this in a new book (it would help if I had any idea what the cause could be).
Can anyone offer a suggestion as to what this could be due to ?