Select range where cells <> "" 3

[ClulessChris]

Hi

I'm using the following to get a range of used cells in a column

Range("A1").select
' This next line should find the last used cell
Range(Sellection,Selection.End(xlDown)).Count

Trouble in that on times (I don't see any reason for this) This method will count far more cells in that col that those used, other it works as I expect and only count those cells used.

Can anybody explane this to me?

many thaks for you input.

Everybody body is somebodys Nutter.

 

[ClulessChris]

I just re-read my post. Sorry for the appalling spelling and grammar.

Everybody body is somebodys Nutter.

 

[SkipVought]

Hi,

Then End method is used to find the last populated cell in a contiguous range or the first populated cell in a range.

You must know something about your data, in order to understand the result.

If you start with A1, and A1 is the ONLY populated cell, you will get 65,536, the cell count in the enire column.

If A1 and A10 are populated, you will get 10.

If A1 thru A3 are populated, you will get 3.

So what does your data look like and what are you trying to accomplish?

There is a difference between counting cells, counting populated cells and finding the LAST cell in a range.

Skip,

_[red][/red]

 

[Bong]

Sometimes, if you want to find the last used row, it can be better to go the other way: Range("a65536").End(xlUp). Also bear in mind that count is a property of a collection. In the case you posted, the collection is all the cells in a range, populated or not. There are, of course, spreadsheet functions to count cells that are not empty (count and counta).

_________________
Bob Rashkin

 

[ClulessChris]

Thanks for your input, I can now see why I have a bug. In some instances I was using this method where it may be possible that only the first cell is populated.
Many thanks SkipVought*
Bong also a usefull post and thanks to you. I'm aware of the spreadsheet functions however I was looking at the VBS methods.

Regards all

Everybody body is somebodys Nutter.

 

[Bong]

And you can use spreadsheet functions in the VBA script:
... application.worksheetfunction.count(range)

_________________
Bob Rashkin