VBA counting autofiltered rows incorrectly

[baitmaster]

Hello all

I have a range of data, and have autofiltered it. I am trying to count the number of rows remaining, using

Range("rosterMatrixAll").SpecialCells(xlCellTypeVisible).Rows.Count

This usually returns the correct value, except in some scenarios, where the first 8 (or so) lines of data are hidden. In this scenario, the value returned = 1

The header row (row 1 of the range) is always visible, its where the autofilter boxes appear, and contains labels. The last row always disappears, its a dummy line to act as the bottom border of the table, and contains no data

Ive tried other things as a test; notably, I split the code, so that the visible range only is selected, and then used selection.rows.count. Im expecting to see 100 or so, because that many rows are now selected. Still I get 1

Are there any known issues that would cause this? It only seems to occur when rows 2:x are hidden by autofilter

I'm using xl2007, but not a "table", as I am trying to maintain backwards compatibility

Thanks

 

[baitmaster]

Bump...

VBA seeing only 1 visible row, if first line of data autofiltered out... Any suggestions for a better way to count # of visible rows in an autofiltered table?

The correct answer is visible next to the statusbar, can I access this?

thanks

 

[baitmaster]

Bump

Autofilter really annoying me now.. has no-one got any suggestions..?

 

[GlennUK]

What is your definition of rosterMatrixAll?

 

[baitmaster]

Thanks for your interest

That was the auotfiltered range, as described
- a rectangular block, all data (no blanks), headers on top row, with autofilter on same header row
- named range, exactly as referred to in code
- blank bottom row, to ensure calculations update when new lines added

I think I needed to refer to the Autofilter object, instead of the named range which was being autofiltered. The following link contains some autofilter counting code, which is returning the correct values. I will integrate this with my code, and hopefully that will work:
http://www.contextures.com/xlautofilter03.html#Count

 

[ukmikeb]

Hi

My reply #4 in this thread -

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

shows an alternative.

hth

 

[baitmaster]

Thanks for the help so far, I have a partial fix based on the previous comments, but am still experiencing related problems, and I think the autofilter feature has a glitch in it...

I have also changed another part of my method of counting, to use subtotal(2,range("").columns(x)), and this is now returning the correct result

My next task is to select the first value from column 1 of the autofiltered list. I thought the following should do it

strStaffUsed = Sheets("staff available").AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells(2, 1).Value

However, this is returning the first value in the unfiltered list, which is currently hidden. Surely this should not happen, because it is not in the visible range?!

Question then, is how to return the first value from an autofiltered list?

 

[baitmaster]

found the result in Mikes previous posts... http://www.mrexcel.com/forum/showthread.php?t=528954

Thanks

 

[ukmikeb]

Hi

Pleased to see that you have solved your problem.

Have a great weekend.