asked on

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 ?

Roy Cox

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

Professor J

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_XResearch

ASKER

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

aikimark

Try this:

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

Open in new window

AL_XResearch

ASKER

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 Cox

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.

aikimark

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_XResearch

ASKER

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

ASKER CERTIFIED SOLUTION

AL_XResearch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AL_XResearch

ASKER

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 Liss

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.