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" wrote in message
...
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