CODEWITHSUNDEEP
excelvba
user2762926
user2762926

Reputation: 11

Select top-n rows in table after filter with VBA

It seems that applying filter to a table has destroyed my understanding of how to handle this.

I have a table with multiple columns. I'm going to filter on one of the columns and sort on another. After that, I want to select/copy the first 10 rows of specific columns of what's filtered into another table.

I could easily do this before filters. I need the first 10 rows AFTER the filter is applied. I'm not seeing how to choose the 10th row AFTER a filter.

Can anyone point me to a VBA reference that explains how to do this? Do I need to use SQL to do this? Am I over thinking this and making it too complicated?

Upvotes: 1

Views: 11244

Answers (2)

AjV Jsy
AjV Jsy

Reputation: 6075

For what it's worth, seeing as this is one of the first search results for this kind of issue -
after filtering a table on a named column like this :

Worksheets("YourDataSheet").ListObjects("Table_Name").Range.AutoFilter _
  field:=Worksheets("YourDataSheet").ListObjects("Table_Name").ListColumns("ColumnName").Index, _
  Criteria1:="FilterFor..."

... I was then able to copy the resulting single visible row to another sheet using :

Worksheets("YourDataSheet").Range("Table_Name").SpecialCells(xlCellTypeVisible).Range("A1").EntireRow.Copy _
  Destination:=Range("AnotherSheet!$A$2").EntireRow

So that's one way to refer to visible rows after the filtering. HTH.

Upvotes: 0

Andy G
Andy G

Reputation: 19367

The following works to select the first 10 visible cells of column F, after filtering is applied. You'll need start at F2 if you want to exclude the header-cell.

Sub TenVisible()
    Dim rng As Range
    Dim rngF As Range
    Dim rng10 As Range

    Set rngF = Range("F:F").SpecialCells(xlCellTypeVisible)

    For Each rng In Range("F:F")
        If Not Intersect(rng, rngF) Is Nothing Then
            If rng10 Is Nothing Then
                Set rng10 = rng
            Else
                Set rng10 = Union(rng10, rng)
            End If
            If rng10.Cells.Count = 10 Then Exit For
        End If
    Next rng
    Debug.Print rng10.Address
    '.. $F$1:$F$2,$F$4:$F$5,$F$9:$F$10,$F$12,$F$20:$F$21,$F$23
    rng10.Select
End Sub

The following is one of a number of ways to select from F2 downwards (assuming the UsedRange starts from row 1):

Set rngF = Range("F2", Cells(ActiveSheet.UsedRange.Rows.Count, _
    Range("F2").Column)).SpecialCells(xlCellTypeVisible)

Upvotes: 4

Related Questions