Range.Formula breaks Worksheetfunction.CountA

[Greg Lovern]

If I run Range.Formula = Range.Formula on a given range of blank
cells, WorksheetfunctionCountA returns 1 for each cell in the range,
indicating occupied cells.

In our project, users paste long text strings that look like numbers
to Excel. Even if I protect the formatting, Excel allows pasting in
formatting, including number formatting. And these users often copy
from other rich-text sources such as Outlook, then paste into Excel,
so what looked like 123456789012 in Outlook looks like 1.234567E+11
when they paste it in. I've discussed paste special | values, and
copying to Notepad,and I even provided an import feature. But of
course they're still pasting from Outlook. Oh, and there are tens of
thousands of users, and growing.

I've been asked to make it automatically convert back to text format,
so on the worksheet's Change event I'm changing the target range's
number formatting back to "@" (which is how we send the workbook out
to users), and since that alone isn't enough, I then do a
range.Formula = range.Formula on the target range, equivalent to
pressing F2 and then Enter. Yes, I realize any leading zeros are lost
forever, along with any digits after the 15th, and I've covered that
with them, but this is what they want.

But today I discovered another problem. The code I inherited when I
was brought in uses Worksheetfunction.CountA to determine the next
blank row. But when I started doing the range.Formula = range.Formula
above, I found that Worksheetfunction.CountA thinks every affected
cell is occupied, even if the cells are blank.


Any thoughts on why that would be? Here's what I plan to try tomorrow
as a workaround -- any other ideas?


Dim vIsRangeEmpty As Variant
Dim iIsRangeEmptyCol As Long
Dim iIsRangeEmptyRow As Long
Dim iUboundIsRangeEmptyCol As Long
Dim iUboundIsRangeEmptyRow As Long


Function IsRangeEmpty(target_range As Range) As Boolean

On Error GoTo GenErr

vIsRangeEmpty = target_range

If Not IsArray(vIsRangeEmpty) Then
'single cell:
If vIsRangeEmpty = "" Then
IsRangeEmpty = True
Else
IsRangeEmpty = False
End If
Exit Function
End If

'multiple cells:
iUboundIsRangeEmptyRow = UBound(vIsRangeEmpty, 1)
iUboundIsRangeEmptyCol = UBound(vIsRangeEmpty, 2)

For iIsRangeEmptyCol = 1 To iUboundIsRangeEmptyCol
For iIsRangeEmptyRow = 1 To iUboundIsRangeEmptyRow
If vIsRangeEmpty(iIsRangeEmptyRow, iIsRangeEmptyCol) <> ""
Then
IsRangeEmpty = False
Exit Function
End If
Next iIsRangeEmptyRow
Next iIsRangeEmptyCol

IsRangeEmpty = True

Exit Function
GenErr:
IsRangeEmpty = False
End Function

 

[Tim Zych]

Will .Value = .Value work for you instead? CountA doesn't seem to mind that.

 

[Greg Lovern]

No, I tried that before trying .Formula = .Formula.

 

[Greg Lovern]

I forgot to mention two things:

-- You might be wondering why it matters since the users are pasting
data onto the target range, and therefore target range would normally
be occupied anyway.

The problem is not when users paste data. The problem is when users
DELETE (clear contents with Delete key) data. The worksheet Change
event doesn't know whether the user pasted or deleted, so I can't skip
the .Formula = .Formula operation when the user deletes data. So when
the user is finished with a set of a data, they delete (clear
contents) that few hundred or so rows. But then CountA thinks those
rows are occupied, so next activity skips down below them, bewildering
and annoying the user. If the user tries to "fix" the problem by
deleting (clear contents) even more rows, they only make the problem
worse.


-- You might be wondering why not just loop through the target range,
doing .Formula = .Formula only on occupied cells.

Often the worksheet Change event's target range will be huge. Looping
through each cell would be much slower than doing .Formula = .Formula
on the whole target range on one step.


Greg

 

[Tim Zych]

But when I started doing the range.Formula = range.Formula

above, I found that Worksheetfunction.CountA thinks every affected
cell is occupied, even if the cells are blank.

What's the issue then? This works around the problem you describe. If I type
in a value, then clear it using the delete key, CountA on A1:A10 works
correctly.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Me.Range("A1:A10"), Target) Is Nothing Then
Application.EnableEvents = False
With Target
.NumberFormat = "@"
.Value = .Value
End With
Application.EnableEvents = True
End If
' MsgBox Application.WorksheetFunction.CountA(Me.Range("A1:A10"))
End Sub

or even

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Me.Range("A1:A10"), Target) Is Nothing Then
Application.EnableEvents = False
Dim rngArea As Range
For Each rngArea In Target.Areas
With rngArea
.NumberFormat = "@"
.Value = .Value
End With
Next
Application.EnableEvents = True
End If
' MsgBox Application.WorksheetFunction.CountA(Me.Range("A1:A10"))
End Sub

--
Tim Zych
www.higherdata.com
Compare data in worksheets and find differences with Workbook Compare
A free, powerful, flexible Excel utility


No, I tried that before trying .Formula = .Formula.

 

[Charles Williams]

Greg,

Its interesting that the .Formula=.Formula trick only confuses COUNTA (and
..end(xlup)) when the cells are formatted as text.

If I have understood the problem correctly you just need to find the last
non-empty cell on a worksheet (oSheet).

Try this: it works fine even after formatting as text and .formula=.formula
(as long as you dont have merged cells)

jLastRow=oSheet.Cells.Find(What:="*", LookIn:=xlFormulas,
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
jLastCol=oSht.Cells.Find(What:="*", LookIn:=xlFormulas,
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Greg Lovern]

You're still using .Value = .Value, which doesn't cause the problem.

I'm using .Formula = .Formula, and need to because that's what makes
the cell contents obey the text formatting after the user entered them
with other formatting.

Also, see Charles' note below that the problem only occurs with text
formatting; I hadn't noticed that text formatting was a requirement
for the problem to occur; I hadn't tested to see if the problem occurs
with other formatting, since I need the text formatting.

Greg

 

[Greg Lovern]

Thanks, but finding the last non-empty cell is *not* what I need. What
I need is to find the first blank row in a given set of columns, even
if there are other occupied cells in random places outside those
columns and/or below the first blank row. Sorry I didn't fully
elaborate that in my OP.

Example:

I need to find the first blank row in columns A:BB. I don't care if
there are random occupied cells to the right of column BB, and I don't
care if there are random occupied cells below the first blank row in
columns A:BB.


Thanks,

Greg

 

[Greg Lovern]

One other detail I noticed last night:

Although .Formula = .Formula is equivalent to pressing F2 and then
Enter, or clicking in the formula bar and then pressing enter, those
keyboard & mouse actions do not cause the problem. I've only seen the
problem when I'm doing .Formula = .Formula.

I suppose that means Sendkeys might work around the problem, but given
that the range is often thousands of cells, sometimes ten or even
hundreds of thousands of cells, doing Sendkeys on each cell would be
crazy.

Greg

 

[Greg Lovern]

Hi Charles,

I just realized I could use your general idea to check for a range
being unoccupied -- do a Find on "*" in xlFormulas. If it returns
Nothing, then the target range is blank. Then, as before, I'd check
each row until I found the first blank one.

Function IsRangeEmpty(target_range As Range) As Boolean

On Error GoTo GenErr

Set rgRet = target_range.Find(What:="*", LookIn:=xlFormulas)

If rgRet Is Nothing Then
IsRangeEmpty = True
Else
IsRangeEmpty = False
End If

Exit Function
GenErr:
IsRangeEmpty = False
End Function


I just tested and it works, and is probably faster than my function
above. Thanks!

Not to mention, "Doh! Why didn't I think of that?!?" (slap head).


Thanks,

Greg

 

[Charles Williams]

Hi Greg,

maybe you can do it in a simpler faster way: try this

jNextEmptyRow = Range("a1:a20").Find(What:="", After:=Range("a1"),
LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Row


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Greg Lovern]

Hi Charles,

Thanks, but I believe that would only work if I was only looking in a
single column ("a1:a20" in your example). But I'm never just looking
in a single column.

If you replaced "a1:a20" with "a1:z20", and entered any data in, say,
cell d1, your code would find cell b1 as the next empty cell and
return row 1 as the next empty row.

I think it would also break if cell a1 was occupied, as it starts the
search after that cell.

Thanks though.

Greg

 

[Greg Lovern]

I have just discovered that this also breaks IsEmpty (also in code I
inherited when I was brought in).

IsEmpty normally returns True on an unoccupied cell. But if you format
the cell as text and run .Formula = .Formula on it, IsEmpty now
returns False, as if the cell is occupied.

Sigh. This is about as fun as trying to keep track of all the things
that turn off CutCopyMode.

Greg

 

[Charles Williams]

Yes as you point out, you would need to run the code column by column and
find the max empty rownum across the columns
(and handle a1 etc).

But it would be quite fast, much faster than any cell-by-cell processing.

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Greg Lovern]

Hi Charles,

This is not cell by cell processing, using this to check row by row
until it returns True:


Function IsRangeEmpty(target_range As Range) As Boolean

On Error GoTo GenErr

Set rgRet = target_range.Find(What:="*", LookIn:=xlFormulas)

If rgRet Is Nothing Then
IsRangeEmpty = True
Else
IsRangeEmpty = False
End If

Exit Function
GenErr:
IsRangeEmpty = False
End Function


I'm not clear on how checking column by column, while keeping track of
the row found in each column, and determining the smallest of those
row numbers found, is faster than checking row by row without tracking
anything, and simply stopping when a blank row is found.

Greg

 

[Charles Williams]

Hi Greg,

Apologies I misread row by row as cell by cell ...

Its probably fast enough already, although with Excel 2007 who knows?

But its hard for me to resist speeding things up so the following code
should be faster ...
'-----------------------------------------------------------------------------
Function getNextEmptyRow(SearchRange As Range) As Long
Dim jStartrow As Long
Dim jEmptyCellRow As Long


If IsRangeEmpty(SearchRange.Rows(1)) Then
getNextEmptyRow = 1
Else
jStartrow = 1
Do
jEmptyCellRow = NextEmptycellRow(SearchRange.Columns(1),
jStartrow)
If jEmptyCellRow > 0 Then
If IsRangeEmpty(SearchRange.Rows(jEmptyCellRow)) Then
getNextEmptyRow = jEmptyCellRow
Else
jStartrow = jEmptyCellRow
End If
End If
Loop Until getNextEmptyRow > 0 Or jEmptyCellRow = 0
End If
End Function

Function IsRangeEmpty(target_range As Range) As Boolean
Dim rgRet As Range

On Error GoTo Finish

Set rgRet = target_range.Find(What:="*", LookIn:=xlFormulas)

If rgRet Is Nothing Then
IsRangeEmpty = True
Else
IsRangeEmpty = False
End If
Set rgRet = Nothing
Exit Function

Finish:
Set rgRet = Nothing
IsRangeEmpty = False
End Function
Function NextEmptycellRow(theColumn As Range, jStartrow As Long) As Long
Dim rgRet As Range

On Error GoTo Finish
If jStartrow > theColumn.Rows.Count Then Exit Function

Set rgRet = theColumn.Find(What:="", After:=theColumn.Cells(jStartrow,
1), LookIn:=xlFormulas, LookAt:= _
xlWhole)
If Not rgRet Is Nothing Then NextEmptycellRow = rgRet.Row

If NextEmptycellRow < jStartrow Then NextEmptycellRow = 0
Finish:
Set rgRet = Nothing
End Function
'------------------------------------------------------------------


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Greg Lovern]

And now I've discovered that it also affects saving a spreadsheet as a
unicode tab-delim text file. A tab character is saved out between each
affected cell (to the right of and below the last actually occupied
cell), making the file hugely padded with tab characters after the
last data.

I'd expect non-unicode tab-delim and CSV to be affected too, but
haven't tested them.


Greg