Row Count

[Matt]

Using code, I have used autofilter to filter the data. I now want to count how many rows that I am left with and pinpoint the activecell to the first cell of the first row of the filtered data. Hope someone can help.

Many thanks

Matt

[Ivan F Moala]

On 2002-05-21 02:19, Matt wrote:
Using code, I have used autofilter to filter the data. I now want to count how many rows that I am left with and pinpoint the activecell to the first cell of the first row of the filtered data. Hope someone can help.

Many thanks

Matt

Hi Matt
This may help you out.
Amend as required.

Sub FilteredRow_Count()

'// IFM

Dim FilteredRg As Areas

Dim rArea As Range

Dim dRowcount As Double

Dim sStCell As String

Dim Flag As Boolean



'// [A2] is the start of the filtered Data

Set FilteredRg = Range([A2], [A2].End(xlDown)).SpecialCells(xlVisible).Areas



'// make sure we hit the right item

Flag = True



For Each rArea In FilteredRg

        '// get 1st cell address

        If Flag Then sStCell = rArea.Item(1).Address: Flag = False

        dRowcount = dRowcount + rArea.Rows.Count

Next



MsgBox dRowcount & ":" & sStCell



'// Cleanup

Set FilteredRg = Nothing



End Sub