Last used cell in column
 

Is there a better way to do this?

Function LastRowOfData(oWs As Worksheet, Column As String) As Long
Dim currWs As Worksheet
Set currWs = ActiveSheet

oWs.Activate
Range(Column & "65536").End(xlUp).Select
Dim oCell As Range
Set oCell = ActiveCell
LastRowOfData = oCell.row

currWs.Activate

End Function

it works but looks like a terrible way to find that info to me?

thanks
mark

Last used cell in column
 

"mp" wrote in message
...
Is there a better way to do this?

Function LastRowOfData(oWs As Worksheet, Column As String) As Long
Dim currWs As Worksheet
Set currWs = ActiveSheet

oWs.Activate
Range(Column & "65536").End(xlUp).Select
Dim oCell As Range
Set oCell = ActiveCell
LastRowOfData = oCell.row

currWs.Activate

End Function

it works but looks like a terrible way to find that info to me?

thanks
mark




I've seen others suggest using .End(xlUp) for quickly finding the last
populated cell in a column, so I think that is fine. Your use of
..Select and .Activate is slowing the process down without reason,
however. It took me a long time to realize what was happening; and I
don't know if the explanation is in the help files or not ... but
because the macro recorder is driven off the user interface (i.e., cells
and worksheets are getting selected and activated) that's the way the
recorder generates code. This will run much faster:

Function LastRowOfData(oWs As Worksheet, Column As String) As Long

Set LastRowOfData = oWs.Range(Column & "65536").End(xlUp).Row

End Function

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Last used cell in column
 

"Clif McIrvin" wrote in message
...
"mp" wrote in message
...
Is there a better way to do this?

Function LastRowOfData(oWs As Worksheet, Column As String) As Long
Dim currWs As Worksheet
Set currWs = ActiveSheet

oWs.Activate
Range(Column & "65536").End(xlUp).Select
Dim oCell As Range
Set oCell = ActiveCell
LastRowOfData = oCell.row

currWs.Activate

End Function

it works but looks like a terrible way to find that info to me?

thanks
mark




I've seen others suggest using .End(xlUp) for quickly finding the last
populated cell in a column, so I think that is fine. Your use of
.Select and .Activate is slowing the process down without reason,
however. It took me a long time to realize what was happening; and I
don't know if the explanation is in the help files or not ... but
because the macro recorder is driven off the user interface (i.e.,
cells and worksheets are getting selected and activated) that's the
way the recorder generates code. This will run much faster:

Function LastRowOfData(oWs As Worksheet, Column As String) As Long

Set LastRowOfData = oWs.Range(Column & "65536").End(xlUp).Row

End Function



For xl2007 and beyond, you need to use 1048576 for the last possible
row.


--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Last used cell in column
 

"Clif McIrvin" wrote in message
...
"mp" wrote in message
...
Is there a better way to do this?

Function LastRowOfData(oWs As Worksheet, Column As String) As Long
Dim currWs As Worksheet
Set currWs = ActiveSheet

oWs.Activate
Range(Column & "65536").End(xlUp).Select
Dim oCell As Range
Set oCell = ActiveCell
LastRowOfData = oCell.row

currWs.Activate

End Function

it works but looks like a terrible way to find that info to me?

thanks
mark




I've seen others suggest using .End(xlUp) for quickly finding the last
populated cell in a column, so I think that is fine. Your use of .Select
and .Activate is slowing the process down without reason, however. It
took me a long time to realize what was happening; and I don't know if the
explanation is in the help files or not ... but because the macro recorder
is driven off the user interface (i.e., cells and worksheets are getting
selected and activated) that's the way the recorder generates code. This
will run much faster:

Function LastRowOfData(oWs As Worksheet, Column As String) As Long

Set LastRowOfData = oWs.Range(Column & "65536").End(xlUp).Row

End Function

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)


Many thanks.
I felt the select and activate were wrong, but had found a similar usage as
a basis for this function of mine.
wasn't sure how to get rid of them.
Thanks
mark

Last used cell in column
 

Clif McIrvin formulated the question :
"Clif McIrvin" wrote in message
...
"mp" wrote in message
...
Is there a better way to do this?

Function LastRowOfData(oWs As Worksheet, Column As String) As Long
Dim currWs As Worksheet
Set currWs = ActiveSheet

oWs.Activate
Range(Column & "65536").End(xlUp).Select
Dim oCell As Range
Set oCell = ActiveCell
LastRowOfData = oCell.row

currWs.Activate

End Function

it works but looks like a terrible way to find that info to me?

thanks
mark




I've seen others suggest using .End(xlUp) for quickly finding the last
populated cell in a column, so I think that is fine. Your use of .Select
and .Activate is slowing the process down without reason, however. It took
me a long time to realize what was happening; and I don't know if the
explanation is in the help files or not ... but because the macro recorder
is driven off the user interface (i.e., cells and worksheets are getting
selected and activated) that's the way the recorder generates code. This
will run much faster:

Function LastRowOfData(oWs As Worksheet, Column As String) As Long

Set LastRowOfData = oWs.Range(Column & "65536").End(xlUp).Row

End Function



For xl2007 and beyond, you need to use 1048576 for the last possible row.

Actually, you could use this to avoid having to hard-code the number of
rows.

LastRowOfData = oWs.Cells(oWs.Rows.Count, _
Columns(Column).Column).End(xlUp).Row

If your function accepted Column As Long instead of a string, it could
be done like this:

LastRowOfData = oWs.Cells(oWs.Rows.Count,
Columns(Column).End(xlUp).Row

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Last used cell in column
 

GS was thinking very hard :
LastRowOfData = oWs.Cells(oWs.Rows.Count, Columns(Column).End(xlUp).Row

Oops! Change to this:

LastRowOfData = oWs.Cells(oWs.Rows.Count, Column).End(xlUp).Row

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Last used cell in column
 

"GS" wrote in message
...
GS was thinking very hard :
LastRowOfData = oWs.Cells(oWs.Rows.Count, Columns(Column).End(xlUp).Row

Oops! Change to this:

LastRowOfData = oWs.Cells(oWs.Rows.Count, Column).End(xlUp).Row

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc



nice addition, thanks
mark

Last used cell in column
 

Just another way to do it

cells.specialcells(xlcelltypelastcell).row

this will return the last used cell's row number.The other process can
get the last row if data is there but if you want to know last
accessed row (where some formatting done) you have to use that
statement.Because it can track the formatted cells as accessed cell.

Last used cell in column
 

"Javed" wrote in message
...
Just another way to do it

cells.specialcells(xlcelltypelastcell).row

this will return the last used cell's row number.The other process can
get the last row if data is there but if you want to know last
accessed row (where some formatting done) you have to use that
statement.Because it can track the formatted cells as accessed cell.

Good to know, thanks
But how do I tell it which column to consider?
Thanks
Mark

Last used cell in column
 

Sorry MP.It is worksheet wide setting.not column based.
I posted it just for littl addition.

Last used cell in column
 

Just another way to do it

cells.specialcells(xlcelltypelastcell).row

this will return the last used cell's row number.

Actually, that is not a good way to find the last row as SpecialCells can be
fooled. Try this experiment. Go to a new sheet that never had any entries
made in it (insert a new sheet would be best), then type an X in B3. Next
type an X in B15, then select B15 and press the Delete key on the keyboard.
Now go into the VB editor and execute your line of code in the Immediate
Window like this...

? cells.specialcells(xlcelltypelastcell).row

It should print out 3, but I am betting it printed out 15 instead. There are
ways to reset the last cell as SpecialCells sees it, but it isn't worth the
effort. By the way, the last row will reset to the correct value next time
the workbook is opened, but the problem is you cannot know if your user will
make an accidental entry (like the X in B15 I had you do) and then,
realizing it, delete it using the Delete key.

Rick Rothstein (MVP - Excel)

Last used cell in column
 

Rick,
In addition, I have had SpecialCells(xlCellTypeLastCell) return multiple cells.
I don't use it at all anymore.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Formats & Styles: lists or removes unused styles & number formats - in the free folder)





"Rick Rothstein"

wrote in message
...

Just another way to do it
cells.specialcells(xlcelltypelastcell).row
this will return the last used cell's row number.


Actually, that is not a good way to find the last row as SpecialCells can be fooled. Try
this experiment. Go to a new sheet that never had any entries made in it (insert a new
sheet would be best), then type an X in B3. Next type an X in B15, then select B15 and
press the Delete key on the keyboard. Now go into the VB editor and execute your line of
code in the Immediate Window like this...

? cells.specialcells(xlcelltypelastcell).row

It should print out 3, but I am betting it printed out 15 instead. There are ways to
reset the last cell as SpecialCells sees it, but it isn't worth the effort. By the way,
the last row will reset to the correct value next time the workbook is opened, but the
problem is you cannot know if your user will make an accidental entry (like the X in B15
I had you do) and then, realizing it, delete it using the Delete key.

Rick Rothstein (MVP - Excel)

Last used cell in column
 

In addition, I have had SpecialCells(xlCellTypeLastCell) return multiple
cells.

Do you mean it returned a range consisting of multiple cells for a single
call to SpecialCells? Or did you mean it returned one cell the first time
you called it and a different cell the next time it was called?

Rick Rothstein (MVP - Excel)

Last used cell in column
 

"GS" wrote in message
...
GS was thinking very hard :
LastRowOfData = oWs.Cells(oWs.Rows.Count, Columns(Column).End(xlUp).Row

Oops! Change to this:

LastRowOfData = oWs.Cells(oWs.Rows.Count, Column).End(xlUp).Row

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


I thought that was working for me last night,
now it's returning the row past the last row with data
I have data in first 10 rows, row 11 is blank
this is returning 11 instead of 10
am I doing something wrong?

I have closed and reopened sheet(in case I had put something in 11 and
deleted(which i don't think i did) )

Function LastRowOfData(oWs As Worksheet, Column As Long) As Long

Dim lastRow As Long
lastRow = oWs.Cells(oWs.Rows.Count, Column).End(xlUp).row
Debug.Print "last row of data = " & lastRow

LastRowOfData = lastRow
End Function

Last used cell in column
 

"mp" wrote in message
...

"GS" wrote in message
...

I thought that was working for me last night,
now it's returning the row past the last row with data
I have data in first 10 rows, row 11 is blank
this is returning 11 instead of 10
am I doing something wrong?

I have closed and reopened sheet(in case I had put something in 11 and
deleted(which i don't think i did) )

Function LastRowOfData(oWs As Worksheet, Column As Long) As Long

Dim lastRow As Long
lastRow = oWs.Cells(oWs.Rows.Count, Column).End(xlUp).row
Debug.Print "last row of data = " & lastRow

LastRowOfData = lastRow
End Function

ok, it gets even weirder...
I put somethign in row 11 and reran the test...
it worked...returned 11 now....
then I deleted the data in 11 and reran...
it works again, now it returned 10
???? wtf ????
oh well,
thanks anyway...

does this indicate it's a buggy way to do this?
thanks
mark

Last used cell in column
 

Yes, "it returned a range consisting of multiple cells for a single call"

I believe I was looking for the last column and LastCell was returning a reference to two
or three cells from the same row.
This was in August 2010 - I did not keep detailed notes, just a message to myself about
don't do that.
I suspect it is a rare occurrence and something could have contributed to it, but I don't
know what.
The UsedRange seems to be a reliable method for finding a general starting area.
'---
Jim Cone




"Rick Rothstein"
wrote in message
...
In addition, I have had SpecialCells(xlCellTypeLastCell) return multiple cells.

Do you mean it returned a range consisting of multiple cells for a single call to
SpecialCells? Or did you mean it returned one cell the first time you called it and a
different cell the next time it was called?

Rick Rothstein (MVP - Excel)

Last used cell in column
 

Yes, "it returned a range consisting of multiple cells for a single call"
I believe I was looking for the last column and LastCell was returning a
reference to two or three cells from the same row.

That is a new one for me, but I do not doubt you at all... SpecialCells can
be flakey.

This was in August 2010 - I did not keep detailed notes, just a message
to myself about don't do that.

LOL... yep, that is a good note to leave for yourself.

The UsedRange seems to be a reliable method for finding a general starting
area.

Yes, it seems to track that pretty well, but that ability is almost never
needed... most of the time Row 1 is used for a header or starting value and,
when it isn't, the user knows that starting row that should be used and it
is easy to just set a constant (Const statement) to it, that way one can use
the more self-documenting name instead.

Rick Rothstein (MVP - Excel)

Last used cell in column
 

mp used his keyboard to write :
"mp" wrote in message
...

"GS" wrote in message
...

I thought that was working for me last night,
now it's returning the row past the last row with data
I have data in first 10 rows, row 11 is blank
this is returning 11 instead of 10
am I doing something wrong?

I have closed and reopened sheet(in case I had put something in 11 and
deleted(which i don't think i did) )

Function LastRowOfData(oWs As Worksheet, Column As Long) As Long

Dim lastRow As Long
lastRow = oWs.Cells(oWs.Rows.Count, Column).End(xlUp).row
Debug.Print "last row of data = " & lastRow

LastRowOfData = lastRow
End Function

ok, it gets even weirder...
I put somethign in row 11 and reran the test...
it worked...returned 11 now....
then I deleted the data in 11 and reran...
it works again, now it returned 10
???? wtf ????
oh well,
thanks anyway...

does this indicate it's a buggy way to do this?
thanks
mark

I suspect that row 11 had a space character in it. Some people have the
very nasty habit to use that for clearing a cell's contents. So, if it
returned 11 then that cell was not empty!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Last used cell in column
 

"GS" wrote in message
...
mp used his keyboard to write :
"mp" wrote in message
...

"GS" wrote in message
...

I thought that was working for me last night,
now it's returning the row past the last row with data
I have data in first 10 rows, row 11 is blank
[]

does this indicate it's a buggy way to do this?
thanks
mark

I suspect that row 11 had a space character in it. Some people have the
very nasty habit to use that for clearing a cell's contents. So, if it
returned 11 then that cell was not empty!

--
Garry


ha, that is entirely possible,
thanks, i never would have thought of that.
mark

Last used cell in column
 

"mp" wrote in message
...

"GS" wrote in message
...
mp used his keyboard to write :
"mp" wrote in message
...

"GS" wrote in message
...
[]


I suspect that row 11 had a space character in it. Some people have the
very nasty habit to use that for clearing a cell's contents. So, if it
returned 11 then that cell was not empty!

--
Garry


ha, that is entirely possible,
thanks, i never would have thought of that.
mark

now that i have re-run the code that fills those ranges,
I see it does put a space in the row after the actual data
I have no idea why it would do that...
but till i get that worked out...
is this a terrible way to workaround the problem of
blank cells that aren't really blank :-) ?

Function LastRowOfData(oWs As Worksheet, Column As Long) As Long
'get rid of spaces in seemingly blank cells
Dim oRng As Range
For Each oRng In oWs.UsedRange
If Len(Trim(oRng.Value)) = 0 Then
oRng.Value = ""
End If
Next oRng

LastRowOfData = oWs.Cells(oWs.Rows.Count, Column).End(xlUp).row

End Function

Last used cell in column
 

mp formulated the question :
"mp" wrote in message
...

"GS" wrote in message
...
mp used his keyboard to write :
"mp" wrote in message
...

"GS" wrote in message
...
[]


I suspect that row 11 had a space character in it. Some people have the
very nasty habit to use that for clearing a cell's contents. So, if it
returned 11 then that cell was not empty!

-- Garry


ha, that is entirely possible,
thanks, i never would have thought of that.
mark

now that i have re-run the code that fills those ranges,
I see it does put a space in the row after the actual data
I have no idea why it would do that...

Post your complet code so we can see what it's doing.

but till i get that worked out...
is this a terrible way to workaround the problem of
blank cells that aren't really blank :-) ?

Function LastRowOfData(oWs As Worksheet, Column As Long) As Long
'get rid of spaces in seemingly blank cells
Dim oRng As Range
For Each oRng In oWs.UsedRange
If Len(Trim(oRng.Value)) = 0 Then
oRng.Value = ""
End If
Next oRng

LastRowOfData = oWs.Cells(oWs.Rows.Count, Column).End(xlUp).row

End Function

Cells are 'Empty' by default. Spaces don't enter themselves into cells.
Something MUST put that value there.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Last used cell in column
 

"GS" wrote in message
...
mp formulated the question :
"mp" wrote in message
...

"GS" wrote in message
...
mp used his keyboard to write :
"mp" wrote in message
...

"GS" wrote in message
...
[]

now that i have re-run the code that fills those ranges,
I see it does put a space in the row after the actual data
I have no idea why it would do that...


Post your complet code so we can see what it's doing.

fwiw the sub that fills the data:
(using cSortedDictionary from Olaf Schmidt...Thanks so much Olaf for all
your fantastic work!!!!!)

Sub UpdatePriceCurrentFunds(CurrentFunds As cSortedDictionary)
Dim qurl As String
Dim i As Integer
Dim sTicker As String
Dim sName As String

'start url
qurl = "http://download.finance.yahoo.com/d/quotes.csv?s="

'add ticker symbols
For i = 0 To CurrentFunds.Count - 1
sName = CurrentFunds.ItemByIndex(i)
sTicker = CurrentFunds.KeyByIndex(i)
qurl = qurl & sTicker & "+"
Next i

'get rid of last +
qurl = Left$(qurl, Len(qurl) - 1)

'add attributes for data to return
qurl = qurl & "&f=sd1l1"
's symbol
'l1 last price
'd1 date

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
DataSheet.Activate
Range("a1").CurrentRegion.ClearContents

With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl,
Destination:=DataSheet.Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.saveData = True
End With

Range("a1").CurrentRegion.TextToColumns Destination:=Range("a1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
_
Semicolon:=False, Comma:=True, Space:=False, other:=False

Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True

End Sub

what I've found after more testing is this
*Sometimes* it *does* put a space in "A13" (the cell after last cell of
data)
*Sometimes* it *does NOT* put a space in "A13"
*Sometimes* it puts all the comma delim strings into ColumnA instead of A,B
and C
(and announces an error about parsing one column blah blah)

The differences occur because i'm editing/debugging deleting the cell
contents and re-running code
multiple times as I get the bugs worked out and add other functions that go
to work after this one is done....

when I delete the cells that this sub fills I get errors about deleting a
query blah blah
so i suspect that the code above will not always put the space in the cell
below if you run it with any given list of tickers....but if you delete the
range and re-run, you may find the anomaly I'm finding...or maybe not
:-)

thanks to everyone for their help and guidance with all these questions.
Mark

but anyway...what about the answer to my question about the workaround for
the space in the cell?
i'm sure it's a terrible way to code for an anomaly but not sure the better
alternative

Last used cell in column
 

Mark,
It looks to me that the query may have an empty row of data, or the
last piece of data contains a space. In this case, test for this and
set LastRow to the first cell found above that contains valid data.

The problem lies in the source of the data. So...

lLastRow = LastRowOfData
Dim n As Long
For n = lLastRow To 1
If Len(Cells(n, 1)) 0 And Not Cells(n, 1) = " " Then _
lLastRow = n: Exit For
Next

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Last used cell in column
 

"GS" wrote in message
...
Mark,
It looks to me that the query may have an empty row of data, or the last
piece of data contains a space. In this case, test for this and set
LastRow to the first cell found above that contains valid data.

The problem lies in the source of the data. So...

lLastRow = LastRowOfData
Dim n As Long
For n = lLastRow To 1
If Len(Cells(n, 1)) 0 And Not Cells(n, 1) = " " Then _
lLastRow = n: Exit For
Next

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc



did you run it and get an 'empty' row?
I'm pretty sure i've run it and gotten a 'correct' return on some
occasions...
I'll check again

yes, If Len(Cells(n, 1)) 0 And Not Cells(n, 1) = " " Then _
is a good way and place to deal with that
Thanks
mark

Last used cell in column
 

If Len(Cells(n, 1)) 0 And Not Cells(n, 1) = " " Then _
is a good way and place to deal with that

Why not just combine the two tests like this...

=If Len(Trim(Cells(n, 1))) Then

Trim will collapse the space to an empty string if it is there and then the
Len function will return 0 for it. Note that I did not bother to explicitly
test if the Len function's return value is greater than zero because that
extra test is not necessary. The Len function can only return zero or
positive whole numbers. A logical expression (normally one where two
expressions are tested for being equal, not equal, greater than, etc.)
evaluates to True or False; however, when a logical expression is required
(such as in an If..Then test), VB considers 0 as False and any non-zero
value as True. If the Len function returns 0, then there are no characters
in the text, so a Len(...)0 would be False (the same number returned by the
Len function itself. On the other hand, if the Len function returns a
non-zero value, then that value must automatically be greater than 0 (Len
cannot return negative values), so the Len(..)0 test would be True, but the
non-zero positive value of the Len function's return value would also be
considered True by the If..Then test. Since these are the only
possibilities, you do not need to specifically test the return value for
being greater than 0 inside the If..Then statement.

Rick Rothstein (MVP - Excel)

Last used cell in column
 

"Rick Rothstein" wrote in message
...
If Len(Cells(n, 1)) 0 And Not Cells(n, 1) = " " Then _
is a good way and place to deal with that

Why not just combine the two tests like this...

=If Len(Trim(Cells(n, 1))) Then

Trim will collapse the space to an empty string if it is there and then
the Len function will return 0 for it. Note that I did not bother to
explicitly test if the Len function's return value is greater than zero
because that extra test is not necessary. The Len function can only return
zero or positive whole numbers. A logical expression (normally one where
two expressions are tested for being equal, not equal, greater than, etc.)
evaluates to True or False; however, when a logical expression is required
(such as in an If..Then test), VB considers 0 as False and any non-zero
value as True. If the Len function returns 0, then there are no characters
in the text, so a Len(...)0 would be False (the same number returned by
the Len function itself. On the other hand, if the Len function returns a
non-zero value, then that value must automatically be greater than 0 (Len
cannot return negative values), so the Len(..)0 test would be True, but
the non-zero positive value of the Len function's return value would also
be considered True by the If..Then test. Since these are the only
possibilities, you do not need to specifically test the return value for
being greater than 0 inside the If..Then statement.

Rick Rothstein (MVP - Excel)

I agree the combination is better and that's actually what i did, also...
I was just acknowleging gs for the idea of testing the string at that point

I also know the 0 is not required, but have actually gotten in the habit
of including it, just because in my warped mind it's more accurate
conceptually...
probably not more accurate...maybe more explicit or self-documenting...
to consider the length to be an integer as opposed to using the "sort of
ETC"
to convert to boolean
....completely unnecessary two characters admittedly
....similar in my mind to avoiding default properties, fwiw
:-)
thanks
mark

Last used cell in column
 

It happens that Rick Rothstein formulated :
If Len(Cells(n, 1)) 0 And Not Cells(n, 1) = " " Then _
is a good way and place to deal with that

Why not just combine the two tests like this...

=If Len(Trim(Cells(n, 1))) Then

Trim will collapse the space to an empty string if it is there and then the
Len function will return 0 for it. Note that I did not bother to explicitly
test if the Len function's return value is greater than zero because that
extra test is not necessary. The Len function can only return zero or
positive whole numbers. A logical expression (normally one where two
expressions are tested for being equal, not equal, greater than, etc.)
evaluates to True or False; however, when a logical expression is required
(such as in an If..Then test), VB considers 0 as False and any non-zero value
as True. If the Len function returns 0, then there are no characters in the
text, so a Len(...)0 would be False (the same number returned by the Len
function itself. On the other hand, if the Len function returns a non-zero
value, then that value must automatically be greater than 0 (Len cannot
return negative values), so the Len(..)0 test would be True, but the
non-zero positive value of the Len function's return value would also be
considered True by the If..Then test. Since these are the only possibilities,
you do not need to specifically test the return value for being greater than
0 inside the If..Then statement.

Rick Rothstein (MVP - Excel)

Excellent! Makes perfect sense to me. (Now why didn't I think of
that?<g)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc