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


On Jun 12, 7:50 am, Greg Lovern wrote:
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

On Jun 12, 12:59 am, "Charles Williams"
wrote:

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 Sitehttp://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