CurrentRegion sometimes returns a single cell

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.

Open in new window

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 ?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
CurrentRegion refers to the cells surrounding the specified cell that contain values. If nocelss contain entries then it will only return one cell.

I believe CurrentRegion does not always work but have never had a problem myself. This might help,

CurrentRegion Substitute
ProfessorJimJamMicrosoft Excel ExpertCommented:
Current Region is like Control + *

if you use macro recorder, then start recorder then select the cell that has data and then press Control + * then you will see in the recorded code the usage of CurrentRegion
this will only work if your data does not have space breaks.
if you want the data to be select to the last used cell, then you might wanna consider using Activesheet.UsedRange method
AL_XResearchAuthor Commented:
Thank you for the replies but please let me reiterate:

There is data on the sheet from A1 to A9
The data is contiguous - in other words no blank rows or columns
Nothing writes to that sheet - it is lookup only
When i am in an Excel session where it fails it will initially work around 10 times before returning just the single cell
When the function does return a single cell and I have tested A2 and B1 (the surrounding cells) and they both have values - so CurrentRegion should at the very least be returning the range A1:B2
With 'CurrentRegion' if you select a blank cell touching a populated cell it will expand to encompass all populated cells in that region - I know as I have tested this
I am very experienced in using 'CurrentRegion' as it is my 'goto method' of selecting the contents of a 'table' where I have the top-left cell and surrounded the table with blank rows and columns

Please also note that the sheet UsedRange property is well known to be unreliable and resetting it can cause other issues. You are better off 'rolling-your-own' via range.find and xlPrevious
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Try this:
Set rngTypeCol = wksLookupSheet.Range(wksLookupSheet.Range("A1"), wksLookupSheet.Range("A1").End(xlDown))

Open in new window

AL_XResearchAuthor Commented:
Thanks 'aikimark' for the response. That formula however will not encompass all rows and columns in the same block as 'A1' - for that you would need the below:

    With wksLookupSheet
        Set rngTypeCol = .Range(wksLookupSheet.Range("A1"), .Range("A1").End(xlDown).End(xlToRight))
    End With

Open in new window

I, however, don't want an alternate method - I am looking for other Excel expert's experiences to find out if anyone has come across this before and or can explain it (other than A1 is not part of a contagious block and the sheet is protected).
Roy CoxGroup Finance ManagerCommented:
I have used CurrentRegion regularlyfor years and never encountered this problem I thought I read somewhere else about issues but I searched Forums, Google and could not find this .

Since the introduction of Tables I have been using them and the built in DataBodyRange replaces using CurrentRegion for me.

If you find anything please share links, etc.
You only mentioned A1:A9.  That is why I posted that version of the code.

Is there a hidden and empty row/column around A1?
Does the code ever error untrapped?
How often is the PC rebooted?
Is the workbook opened by two or more users simultaneously?
AL_XResearchAuthor Commented:
aikimark: The "A1:A9" was a typo, sorry. if you look at my initial posting:

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.

Is there a hidden and empty row/column around A1? No - complete &  solid block

Does the code ever error untrapped? No quite sure what you mean here. The code never errors - just simply returns a single cell rather than the range of the data block

How often is the PC rebooted? It has been rebooted whilst testing - this made no difference.

Is the workbook opened by two or more users simultaneously? No
AL_XResearchAuthor Commented:
Very Strange !

Just tried this code again (without making ANY code or worksheet changes) and it now works without an error !

Guess it must be one of those irritating Excel oddities we have all had from time to time

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AL_XResearchAuthor Commented:
I agree this should be closed / deleted (can I do this myself in future ? ) as I have been unable to reproduced and the issue has not occurred again.
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.