How to Retrieve AutoFiltered Rows?

[louisa888]

hi,
The following is my code.

oSheet.Range("A1&quot.AutoFilter field:=1, _
Criteria1:="Application", _
VisibleDropDown:=False

I'm trying to find the rows in first Column which contains the string "Application". After using the AutoFilter, what is the method to retrieve the rows of the autofiltered rows?
I have tried this:
oSheet.AutoFilter.Range.Address
But it just keep returns me all rows.
Any hint of how I can get the specified rows?

Thanks in advance.

 

[TeddB]

louisa888, it seems you and I have a similar issue.
See post:

AutoFilter issue
thread707-198258
=================
ilses writes:
Ted,

It should be possible, toying with the recorder turned up something like:


Dim myRange As Range
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="rood"
Set myRange = Selection.CurrentRegion.SpecialCells(xlCellTypeVisible)
myRange.Copy
Range("G13&quot.Select
ActiveSheet.Paste
End Sub



Ilses

 

[SkipVought]

Hi,
What do you want to do with the filtered rows? You can attach additional properties to the table selection as follows...

Selection.SpecialCells(xlCellTypeVisible)

With that code you can access ONLY the visible cells in the selection.

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[louisa888]

Hi,
Thanks for the reply. However, I've tried

Set Range = Selection.SpecialCells(xlCellTypeVisible)
Range.Rows.Count
only equals to 1 but I'm expecting the result to be 10.

What I want is that say from Excel, when I filter out the first column to a specified String, it display only the specified rows. I want to retrieve only those rows from VBA.

Thanks

 

[SkipVought]

Why not loop thru the values in that column...

'I have a column with a heading value in row 1 of Names
'I have named the data in that column Names via Insert/Name/Create - Top
    Dim sTestValue As String    'your criteria value
    Dim iColCount, iCol
    
    iColCount = Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Columns.Count
    For Each cell In [Names]
        With cell
            If .Value = sTestValue Then
                For iCol = 1 To iColCount
                    x = Cells(.Row, iCol).Value
                Next iCol
            End If
        End With
    Next

hope this helps
Skip,
metzgsk@voughtaircraft.com

 

[louisa888]

Hi,
I got it working now. It is because I forgot to enable Autofilter. I got another question now is if it is possible to get back the specified row number?
say for example, I know that row 29-39 is the autofiltered cells.
I want to loop through that part to get the cell names. I at first tried to just loop through the range that i got out
from
Set myRange = oSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
for i = 1 to myRange.rows.count
However, I realized that even though i autofiltered but it'll continues to only return me the first 10 rows. Is it possible that i can get the row index or row number? so that i know it's starting from 29 instead of just from 1??

 

[SkipVought]

Yes...

Any Range object has a number of Properties like Row, Rows, Count etc.

So if you have defined the Range...

   With MyRange
      FirstRow = .Row
      LastRow = .Rows.Count + FirstRow - 1
      FirstCol = .Column
      LastCol = .Columns.Count + FirstCol - 1
   End With
' and then...
   For iRow = FirstRow to LastRow
      For iCol = FirstCol to LastCol
         x = Cells(lRow, iCol).Value
      Next
   Next

Hope this helps ;-) Skip,
metzgsk@voughtaircraft.com