Solved: Counting rows in an autofilter

[ilovelunch]

Hi,

I am trying to edit values in a column using an autofilter and an error occurs when there are no rows selected. Is there a way to count how many rows have been selected? Or is there a completely different way to do this?

My code is:
[VBA]Selection.AutoFilter Field:=3, Criteria1:="A"
Range("A1").Offset(1, 2).Range(Selection, Selection.End(xlDown)) = "B"[/VBA]

Thanks

[austenr]

Hi and welcome to VBAX!! Is this what you are looking for?

[vba]

Sub Count_Filtered_Rows()
Dim UpperLeftCorner As Range
' UpperLeftCorner should be set to the upper-left
' corner of the list range:
Set UpperLeftCorner = Sheets("Sheet1").Range("A1")
rowcount = -1
For Each area In _
UpperLeftCorner.CurrentRegion.SpecialCells(xlVisible).Areas
rowcount = rowcount + area.Rows.Count
Next
MsgBox rowcount
End Sub[/vba]

[ilovelunch]

Hi austenr,

I'll have a look at that and see if it is what I need.

Thanks

[ilovelunch]

Hi again,

I'm being rubbish at this time of night. Hope someone can do this quickly for me.
The code that austenr has provided is great but i'm trying to call a method for it and return the value.

Something like this (but can someone make it work please - I've only just started VBA):

[VBA]
(in code)
...
Selection.AutoFilter Field:=3, Criteria1:="A"

RowCount = Count_Filtered_Rows()
If RowCount >0 Then
Range("A1").Offset(1, 2).Range(Selection, Selection.End(xlDown)) = "B"
EndIf
...





Function Count_Filtered_Rows()
Dim UpperLeftCorner As Range
' UpperLeftCorner should be set to the upper-left
' corner of the list range:
Set UpperLeftCorner = Range("A1")
RowCount = -1
For Each area In _
UpperLeftCorner.CurrentRegion.SpecialCells(xlVisible).Areas
RowCount = RowCount + area.Rows.Count
Next
End Function
[/VBA]

Thanks

[Zack Barresse]

Also, have a look at the WorksheetFunction.Subtotal function.

[ilovelunch]

Hi firefytr,

Exactly what I need, thanks a lot!

[Zack Barresse]

You're welcome very much!

Don't forget to mark the thread as Solved!

Take care!