WorksheetFunction.Count HELP NEEDED!

[oakwood_001]

Hi All,

I'm trying to assign the number of cells (blank or non blank) in a given range to a variable but everytime I try, I get a runtime 1004 error.

Can you help?

Dim strStartingConCell As String
Dim strStartingUniqueCell As String
Dim x As Integer
Dim y As Integer

strStartingConCell = InputBox("Starting Concatenated Cell")
strStartingUniqueCell = InputBox("Starting Unique Cell")

y = WorksheetFunction.Count(Range(strStartingConCell, Selection.End(xlDown))
x = WorksheetFunction.Count(Range(strStartingUniqueCell, Selection.End(xlDown))

I think you get what I'm trying to do from this (I hope).

I also tried selecting each cell first. Then tried selecting the range first but it didn't like Count(Range(Selection))

Any help is very much appreciated.

Thanks.

 

[DonkeyOte]

selection will refer to active cell... is this what you want or do you want:

y = WorksheetFunction.Count(Range(Range(strStartingConCell),Range(strStartingConCell).End(xlDown))

 

[RoryA]

First, you shouldn't use Integers as you might have more cells than that - use Long instead.
Second, you wouldn't use Range(Selection) - Selection is a range, so you just use Selection.
Third, if you want to count the number of cells, regardless of whether they are empty, then you don't want the COUNT worksheet function since that only counts numbers. You want the Range object's Count property.

 

[oakwood_001]

Thanks DonkeyOte

I don't get the error now but both x and y are equal to 0 when running this.

They should be equal to ~3000 and ~2000 respectively.

Is it because of my strings? The input box asks for a cell referenfce - i.e. Q4

Would that therefore work?

 

[oakwood_001]

So rorya,

I could just use:

y = Range(Range(strStartingConCell), Range(strStartingConCell).End(xlDown)).Count

And that would do it?

 

[RoryA]

Correct.

 

[oakwood_001]

Hurray!

Thanks to both of you - rorya - wouldnt have realised I needed to use the Count property of the range and - Donkey - wouldnt have realised thats how I needed to specify the range