Why doesn't this work then eh?

[Mike Blackman]

Hi All,

Anyone have any idea why this doesn't work and can anyone think of any efficient ways of counting the number of hidden rows without looping through the usedrange?

wks.Columns.Count - wks.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count

FYI... the problem is that when there is are hidden row(s) its returning rows.count - the last row before the first hidden row.

 

[Richard Schollar]

Talking rubbish as per usual, obviously!

So why doesn't it work with Cells.Count but does with Count?

 

[RoryA]

I suspect it has more to do with the fact that you changed Columns.Count to Rows.Count at the start?

 

[Mike Blackman]

Hi Rory,

My initial example code was flawed agree'd but the code I was physically working on wasn't (I don't think ).... Try this on a worksheet that has hidden rows.

Sub Test()

MsgBox Rows.Count - Columns(1).SpecialCells(xlCellTypeVisible).Rows.Count
MsgBox Rows.Count - Columns(1).SpecialCells(xlCellTypeVisible).Count

End Sub

 

[Mike Blackman]

However it does work with cells not rows....

Sub Test()

MsgBox Rows.Count - Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count
MsgBox Rows.Count - Columns(1).SpecialCells(xlCellTypeVisible).Count

End Sub

Oh god my head hurts again!!!!

 

[RoryA]

I suspect Cells.count (or just Count as they are the same thing) works because it is unequivocal. Rows.Count only returns the number of rows in the first area presumably because of the possibility of overlap of rows - e.g. if your range encompassed different shaped ranges or ranges that started in different places. For example, if you had A5:A10 and C1:C9 selected, what would you expect rows.count to return?