Counting visible rows

[atledreier]

Hello.

I've searched and tried most of the day today, but so far I haven't
found a solution.

I have an autofiltered list where I need to perform operations on the
visible data only. It's some quite extensive lookup functions, and can
take a while to run. So I want to have my macro run on the visible
data only, not the entire 1000x70 table.

So far I have been able to determine the rownumber of the last piece
of data by using

Last = Cells(Rows.Count, 1).End(xlUp).Row

I can't figure out why
Last = UsedRange.Rows.SpecialCells(xlVisible, xlTextValues).Count
won't work, though.

How can I determine which row numbers to run my macro on? The data is
sorted, but not consecutive.
Maybe there is a way to determine if a row is visible before running
the code on it
Maybe there is a way to determine the first and last visible row, and
run the code on all rows in between, should be faster than running it
on all, but not very elegant.

 

[Peter T]

With ActiveSheet.UsedRange
visRows = Application.Intersect(.Columns(1),
..SpecialCells(xlCellTypeVisible)).Count
End With

But I don't think that's what you want, maybe -

For r = firstRow To lastRow
If Not Rows(r).Hidden Then
For c = leftCol To rightCol
'process Cells(r, c)
Next
End If
Next

Regards,
Peter T