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



On Jun 11, 7:47*pm, Greg Lovern wrote:
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