Getting row numbers of a filtered dataset

[MassimoM]

Hello VBA guru,
I am new in VBA in Excel, and I was looking for a way to get row
numbers of a filtered dataset. I mean, I have 1000 rows in my original
dataset I filter them and I would like to get which rows are in the
filtered dataset.
I made a search in the ng but didn't find the solution. Since I manage
to hack it, I would like to share it. Pls don't blame if the code is
not optimized (as well as my english!), as I said I am newbe about it.

MassimoM

Public DefCriteriaRange As Variant
Public filteredRows As New Collection
-------------------------------------------

Private Sub btn_Filtra_Click()

Dim filteredRange As Range
Dim completeData As Range

Set completeDataSet = Range("A13:X882")


' Filter using criteria defined in DefCriteriaRange variable
completeDataSet.AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range(DefCriteriaRange), Unique:=False

' get the range of filtered data using visible cell criteria
Set filteredRange = completeDataSet.SpecialCells(xlVisible)

' Reset the collection
For i = 1 To filteredRows.Count
filteredRows.Remove (1)
Next i

' get addresses of filtered rows
addressesFilteredRows =
completeDataSet.SpecialCells(xlVisible).Address
' split the addresses
addressesSplitted = Split(addressesFilteredRows, ",")
' # of rows in the filtered range
nFilteredRows = completeDataSet.Columns(1).SpecialCells(xlVisible).Count
- 1

' get rows numebers

For i = 1 To nFilteredRows
filteredRows.Add Range(addressesSplitted(i)).Row
Next i

' filteredRows contains the rows numbers as long.

 

[Tom Ogilvy]

This would only include the first row for multirow areas that are visible.

For example, if
row 3:5 were visible, your collection would only hold 3

my visible rows were:
$A$13:$X$16,$A$21:$X$21,$A$24:$X$33

Your code showed:

13
21
24

 

[MassimoM]

Tom,
you are right.Thanks for pointing out this issue. The following
version should fix it:


Set completeDataSet = Range("A13:X882")

completeDataSet.AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range(DefCriteriaRange), Unique:=False

nRows = Range("A13").CurrentRegion.Rows.Count

Set filteredRange = completeDataSet.SpecialCells(xlVisible)

For i = 1 To filteredRows.Count
filteredRows.Remove (1)
Next i

nAreas = completeDataSet.Columns(1).SpecialCells(xlVisible).Areas.Count

For i = 1 To nAreas
filteredRows.Add
completeDataSet.Columns(1).SpecialCells(xlVisible).Areas(i).Row
nRowsInArea =
completeDataSet.Columns(1).SpecialCells(xlVisible).Areas(i).Rows.Count
If nRowsInArea > 1 Then
For j = 1 To nRowsInArea - 1
filteredRows.Add
completeDataSet.Columns(1).SpecialCells(xlVisible).Areas(i).Row + j
Next j
End If
Next i
filteredRows.Remove (1)


Regards,
MassimoM