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:
Selection.AutoFilter Field:=3, Criteria1:="A"
Range("A1").Offset(1, 2).Range(Selection, Selection.End(xlDown)) = "B"

Thanks :bow:

[austenr]

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



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

[ilovelunch]

Hi austenr,

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

Thanks :beerchug:

[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):


(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


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!