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]

Hi Mikey

Try this:


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

 

[Norie]

Eh, how are the rows being hidden?

 

[Misca]

Change the wks.Columns.Count to wks.Rows.Count if you're interested in hidden rows:

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

 

[Sektor]

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

 

[baitmaster]

why are they hidden? Is it anything to do with Autofilter? I kept hitting a similar problem, to the point I now copy the visible range to a "data transfer" worksheet, then do calculations etc on it

http://www.mrexcel.com/forum/showthread.php?t=531585

 

[Mike Blackman]

Meh.... Thanks Richard... dropping .Cells seems to of worked a treat! Conceptually why does it matter about the .Cells though?

@Norie, I was testing with actual Hidden Rows although the method after losing .cells works with both which is conveniently what I'm after!

@Misca, appreciate the example posted doesn't make logical sense, the actual code is however properly constructed.

 

[Richard Schollar]

I presume it's something to do with the SpecialCells method returning a range collection and a range collection not having a Cells property (so VBA defaults to the first area in the collection) giving you the wrong value. Collections do have count properties though so using Count not prefixed with Cells seems to work.

Summat like that anyhow

Probably if Mary (Rory) or The Beard (Colin) drop by they can provide a definitive answer.

 

[Mike Blackman]

That makes sense, god that hurt my head, just couldn't fathom why it wouldn't work!!!

 

[RoryA]

Richard,
What are you talking about?
Specialcells returns a Range object, not a collection of range objects, and does have a Cells property.