Hi, this is a strange one...the following items reside in column 1 from rwos
1 to 10:

Local cash
Local bond
Local equity
Local property
Foreign currency
Foreign bonds
Foreign Equity
Foreigh property
Alternative
Commodity


My code wants the number of items (i.e. the number of non empty rows:

bNoItems = Sheets(sSheetName & "_Items").UsedRange.Rows.Count

Answer comes back 13 not 10! Also when I try and select it it selects 3
empty cells on the end (aty least it's consistent!). Anyone any idea what
may be wrong/with a better way of achieving the count, thanks, Mark

Usedrange has a known bug in that it holds on to previous values. See
http://www.contextures.com/xlfaqApp.html#Unused

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Mark Stephens" wrote in message
...
Hi, this is a strange one...the following items reside in column 1 from
rwos 1 to 10:

Local cash
Local bond
Local equity
Local property
Foreign currency
Foreign bonds
Foreign Equity
Foreigh property
Alternative
Commodity


My code wants the number of items (i.e. the number of non empty rows:

bNoItems = Sheets(sSheetName & "_Items").UsedRange.Rows.Count

Answer comes back 13 not 10! Also when I try and select it it selects 3
empty cells on the end (aty least it's consistent!). Anyone any idea what
may be wrong/with a better way of achieving the count, thanks, Mark

one way i would count nonempty cells if the usedrange method is
unreliable:

counter = 1
do while isempty(.cell(counter,1).value)
counter = counter +1
end loop

the counter variable should now give you an accurate number of cells
that are not empty. this is assuming there are no empty cells inbetween
the cells that contain data

hth
bst

"Mark Stephens" wrote in
:


Hi, this is a strange one...the following items reside in column 1
from rwos 1 to 10:

Local cash
Local bond
Local equity
Local property
Foreign currency
Foreign bonds
Foreign Equity
Foreigh property
Alternative
Commodity


My code wants the number of items (i.e. the number of non empty rows:

bNoItems = Sheets(sSheetName & "_Items").UsedRange.Rows.Count

Answer comes back 13 not 10! Also when I try and select it it selects
3 empty cells on the end (aty least it's consistent!). Anyone any idea
what may be wrong/with a better way of achieving the count, thanks,
Mark

the code below should read not isempty.

sorry for the mistake

bst
bst wrote in news:Xns9ACF87B432CC6nonenonecom@
140.99.99.130:

one way i would count nonempty cells if the usedrange method is
unreliable:

counter = 1
do while isempty(.cell(counter,1).value)
counter = counter +1
end loop

the counter variable should now give you an accurate number of cells
that are not empty. this is assuming there are no empty cells
inbetween
the cells that contain data

hth
bst

"Mark Stephens" wrote in
:


Hi, this is a strange one...the following items reside in column 1
from rwos 1 to 10:

Local cash
Local bond
Local equity
Local property
Foreign currency
Foreign bonds
Foreign Equity
Foreigh property
Alternative
Commodity


My code wants the number of items (i.e. the number of non empty rows:

bNoItems = Sheets(sSheetName & "_Items").UsedRange.Rows.Count

Answer comes back 13 not 10! Also when I try and select it it selects
3 empty cells on the end (aty least it's consistent!). Anyone any
idea
what may be wrong/with a better way of achieving the count, thanks,
Mark

Thanks bst, that's the way I did it although I used a Do Loop which counts
the rows and exits when the row value is ""

Do

bRow = bRow + 1

If Sheets("DataEntry").Range("E" & bRow).Value = "" Then
pbNoFunds = bRow - 1
Exit Do
End if


Loop




Regards, Mark
"bst" wrote in message
...
one way i would count nonempty cells if the usedrange method is
unreliable:

counter = 1
do while isempty(.cell(counter,1).value)
counter = counter +1
end loop

the counter variable should now give you an accurate number of cells
that are not empty. this is assuming there are no empty cells inbetween
the cells that contain data

hth
bst

"Mark Stephens" wrote in
:


Hi, this is a strange one...the following items reside in column 1
from rwos 1 to 10:

Local cash
Local bond
Local equity
Local property
Foreign currency
Foreign bonds
Foreign Equity
Foreigh property
Alternative
Commodity


My code wants the number of items (i.e. the number of non empty rows:

bNoItems = Sheets(sSheetName & "_Items").UsedRange.Rows.Count

Answer comes back 13 not 10! Also when I try and select it it selects
3 empty cells on the end (aty least it's consistent!). Anyone any idea
what may be wrong/with a better way of achieving the count, thanks,
Mark