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


On Jun 12, 2:16 pm, "Charles Williams"
wrote:
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 Sitehttp://www.decisionmodels.com

"Greg Lovern" wrote in message

...

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

On Jun 12, 10:20 am, "Charles Williams"
wrote:
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 Sitehttp://www.decisionmodels.com

"Greg Lovern" wrote in message

...

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