Used range row count throwing up a wierd result, slects wrong as well!

M

Mark Stephens

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
 
B

bst

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
 
M

Mark Stephens

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
 
Top