Count Rows Error

[johnske]

Can anyone tell me why this gives an incorrect row count? (number of rows > 1 always returns 1)


Sub CellTypeConstants()
MsgBox Cells.SpecialCells(xlCellTypeConstants).EntireRow.Rows.Count
End Sub

[Killian]

Hi :hi:

Well, my understanding is that SpecialCells returns a range of the matches, so you would only need to return a count on that range, not the EntireRows.Rows property


Sub CellTypeConstants()
MsgBox Cells.SpecialCells(xlCellTypeConstants).Count
End Sub

[johnske]

Hi Killian,

Thanks. Yes, I can get a count of the cells ok (I should've mentioned that works ok).

But I have a specific instance where I want to use this (special cells) method to count the number of rows that contain one of the type of special cells I choose (xlCellTypeConstants in the case given above, but note that eventually I'll not be restricting this to the single case of xlCellTypeConstants)

Regards,
John :)

[ALe]

In that way if the cells are in the same row, you get 2 instead of one. If you need to count the rows you have to call set the range first and then count its rows.

[johnske]

In that way if the cells are in the same row, you get 2 instead of one. If you need to count the rows you have to call set the range first and then count its rows.Sorry, that doesnt work, I have three rows, the address of these 3 rows is returned, but the number of rows is still given as one.


Sub CellTypeConstants()
Dim Rho As Range
Set Rho = Cells.SpecialCells(xlCellTypeConstants).EntireRow
MsgBox Rho.Address
MsgBox Rho.Rows.Count
Set Rho = Nothing
End Sub

Regards,
John :)

[Killian]

Well its a tricky one, isn't it?
Becuase the range returned can be non-contiguous, you end up with looking at the first cell. The same seems to apply if you use Union on them all (which I guess is what is returned anyway) and Areas don't help either.
The only way around this that I can see is to test the row properties of each cell in the range against each other. The quickest way I think is to add to a collection if the row isn't already in it and then count the items you finish with.
Seems a bit long winded but appears to work


Sub CellTypeConstants()
Dim myRange As Range
Dim c As Range
Dim colCells As New Collection
Dim blnExists As Boolean
Dim item
Set myRange = Cells.SpecialCells(xlCellTypeConstants)
For Each c In myRange
blnExists = False
For Each item In colCells
If c.Row = item.Row Then blnExists = True
Next
If Not blnExists Then colCells.Add c
Next c
MsgBox colCells.Count
End Sub

[Bob Phillips]

MsgBox Intersect(Range("A:A"),Cells.SpecialCells(xlCellTypeConstants).EntireRow).Count

[MWE]

Well its a tricky one, isn't it?
Becuase the range returned can be non-contiguous, you end up with looking at the first cell. The same seems to apply if you use Union on them all (which I guess is what is returned anyway) and Areas don't help either.
The only way around this that I can see is to test the row properties of each cell in the range against each other. The quickest way I think is to add to a collection if the row isn't already in it and then count the items you finish with.
Seems a bit long winded but appears to work


Sub CellTypeConstants()
Dim myRange As Range
Dim c As Range
Dim colCells As New Collection
Dim blnExists As Boolean
Dim item
Set myRange = Cells.SpecialCells(xlCellTypeConstants)
For Each c In myRange
blnExists = False
For Each item In colCells
If c.Row = item.Row Then blnExists = True
Next
If Not blnExists Then colCells.Add c
Next c
MsgBox colCells.Count
End Sub



This solution contains a statement concept that has always bothered me. Why use
If Not blnExists Then colCells.Add c
instead of
If blnExists = False Then colCells.Add c

Is there something inherently more efficient about the former?


The code below violates the rule "Thou shalt not use Go Tos", but it is more efficient code because it stops testing as soon as any row in "c" is = to item.row


Sub CellTypeConstants()
Dim myRange As Range
Dim c As Range
Dim colCells As New Collection
' Dim blnExists As Boolean
Dim item
Set myRange = Cells.SpecialCells(xlCellTypeConstants)
For Each c In myRange
' blnExists = False
For Each item In colCells
If c.Row = item.Row Then goto Nextc
Next
colCells.Add c
Nextc:
Next c
MsgBox colCells.Count
End Sub

[Bob Phillips]

This solution contains a statement concept that has always bothered me. Why use
If Not blnExists Then colCells.Add c
instead of
If blnExists = False Then colCells.Add c

It is more logical to test a Boolean for Is or Is Not.

[johnske]

Ok, thanx guys,

I now have several solutions that give the correct count. Bobs is the shortest and will probably be simpler for the intended purpose. (You'll all find out what that is eventually when I finish the article)

Many thanx to all,
John :thumb

[MWE]

It is more logical to test a Boolean for Is or Is Not.


hmmm ... and in what way is " = False" different from " Is Not" ? Ignoring the silly debate about Boolean purity :devil: and looking at code "readability" or"understandability", it is more "logical" to test for "= True" vs "= False". Yes, I know that the "true" is embedded in the If statement, never-the-less ...

[Killian]

I didn't think that there would be any difference in terms of efficiency... both are doing the same test for equality after all but a quick test over several million iterations revealed "If Not boolean" to be twice a fast as "If boolean = False", so I gues the compiler prefers it...