CurrentRegion and cells not really empty

**Nelson Johnson** · 01-12-2012, 06:53 AM

I have successfully used CurrentRegion - both in VB & on worksheets using keyboard shortcut Ctrl+Shift+8 - for many years. However, a new scenario has come to light in which CurrentRegion does not work as would normally be expected. Lets say that on a worksheet, cells A1:A20 contain the formula

=IF(ROW()>15,"","include")

This results in "include" showing on rows 1 to 15, and rows 16 to 20 appear to be 'empty'. With the formulas still in them, CurrentRegion of A1 would include all 20 rows, as expected. However, if you change all the formulas to values using copy, Paste Special, Values, rows 16 to 20 would be expected to be empty - but CurrentRegion of A1 still includes all 20 rows. After changing to values, use of the =ISBLANK() formula pointing at any apparently 'empty' cell says it's not blank. ISEMPTY() in VB also says such a cell is not empty. Using F2 to select the contents of such a cell, suggests it's got nothing in it: from there, hitting ESC results in it still not being empty, but hitting ENTER changes it to truly being empty. In VB, using .ClearContents also truly empties them. For such 'not really empty' cells, can CurrentRegion be made to work as expected in any more generic, better ways, such as formatting or a different kind of formula?

**Richard Buttrey** · 01-12-2012, 06:56 AM

Hi, and welcome to the forum

I suggest you upload the workbook so that we can see exactly what you're talking about.

Regards

**Nelson Johnson** · 01-12-2012, 07:27 AM

Hi Richard. Many thanks for your quick reply.

Attached is a sample workbook demonstrating this dilemma. A Random whole number generated in cell A2 gives fresh sets of empty rows etc. Cells D1:H15 contain the formulas. If you copy that range and either Paste Special Values to the same cells, or even to cells K1:O15, the CurrentRegion of the range as values still includes apparently empty cells.

Use of keyboard shortcut Ctrl+DownArrow from any cell in row 1 of the range as values, also always takes you to lowermost row 15, even when there are other apparently empty rows above it. This suggests that the various ...Arrow keyboard shortcuts appear to work similarly to CurrentRegion in this respect.

Regards,
Nelson

**TMShucks** · 01-12-2012, 07:35 AM

If I press Ctrl-End in your sample workbook, it goes to cell O16. Row 16 appears to be blank.

I delete row 16 and, in the Immediate Window in the VBA (Alt-F11, Ctrl-G), I type "activesheet.usedrange" without quotes and press enter.

Then, if I press Ctrl-End in your sample workbook, it goes to cell O15.

I think it's just a case of Excel keeping a "memory" of the maximum number of columns and rows and you need to pro-actively make it forget.

Regards, TMS

**Nelson Johnson** · 01-12-2012, 07:45 AM

Hi TMS - many thanks for your advice. It's the conclusion I have more or less come to so far.

Does anyone else have further thoughts?

**Richard Buttrey** · 01-12-2012, 08:54 AM

AFAIAA this is entirely normal behaviour. At some stage you will have had something in row 16. Select and delete row 16 and immediately save the file.
Now if you do End Home you'll see the last cell is O15. Repeat with row 15 and you'll see initially the last cell is still row 15 but immediately after you save the file it will be O14

Regards

**Nelson Johnson** · 01-12-2012, 09:09 AM

Thanks Richard. These results re the used range are indeed familiar to me, and I did remove something from row 16 just prior to attaching the workbook. It's a shame the CurrentRegion as per my original query doesn't also get 'fixed' the same way as UsedRange, by saving.