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

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

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


"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

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

On Jun 11, 8:11*pm, "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote:
Will .Value = .Value work for you instead? CountA doesn't seem to mind that.

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

"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- Hide quoted text -

- Show quoted text -

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

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


"Greg Lovern" wrote in message
...
No, I tried that before trying .Formula = .Formula.

On Jun 11, 8:11 pm, "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote:
Will .Value = .Value work for you instead? CountA doesn't seem to mind
that.

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

"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- Hide quoted text -

- Show quoted text -

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

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


On Jun 11, 11:00 pm, "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote:
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 Zychwww.higherdata.com
Compare data in worksheets and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"Greg Lovern" wrote in message

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

On Jun 11, 8:11 pm, "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote:

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

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

"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- Hide quoted text -

- Show quoted text -

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

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

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

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

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" 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

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

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



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

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" 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

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

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