rng.SpecialCells(xlCellTypeVisible).Rows.Count - not working?

[Tom_H]

Hi

I found a strange behaviour in my vba code...

I have a non sorted table (see below). Obs the last row is ART_ID = 3.

ART_ID    Program_ID
3         194   
3         135
3         154
2         180
2         150
2         126
3         180

I filter the table and use Range to select the rows wiht ART_ID = 3.
This range is sent to a Access database. The "c" is to only select 2 columns of total 5 in the table.

Set rng = tbl.DataBodyRange.Resize(, c).SpecialCells(xlCellTypeVisible)
rng.Select
rng.Copy Destination:=Sheets("DB").Cells(20, 15)
c = rng.SpecialCells(xlCellTypeVisible).Rows.Count

rng.Select >> selects all the 4 rows
rng.Copy >> copies all the 4 rows
but the c = rng... rows.count >> only count 3 rows???
WHY?!

I found a workaround , sorting the table before "Set" the rng-Range.
This works buts doesn't feels right. If I had 100 rows I would never found this error.

 

[Peter_SSs]

Yes, that will only count the rows in the first visible 'area'.
Try this instead

c = tbl.DataBodyRange.Columns(1).SpecialCells(xlVisible).Cells.Count

 

[Tom_H]

Yes!
I have a function that add a Range to a Access database. The function loops trough all rows in the range and creates recordsets.
If I count the rows in the Table instead of the Range I could send the numbers of rows to the function with the Range and it will work!
Thanks!