Last used cell in column

[GS]

mp formulated the question :

mp used his keyboard to write :

news:[email protected]... []

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.

 

[mp]

mp formulated the question :

mp used his keyboard to write :

[]

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

 

[GS]

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

 

[mp]

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

 

[Rick Rothstein]

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)

 

[mp]

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

 

[GS]

It happens that Rick Rothstein formulated :

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