CODEWITHSUNDEEP
vbaexcelautofilter
Pherdindy
Pherdindy

Reputation: 1178

How to select the filtered data without including the column names using excel VBA?

Below is the code that I came up with, however, the issue lies in the 3rd line wherein the filtered data does not necessarily start in row 2. If the first data point satisfying the criteria is located at row 150, then it would return an error:

Error

Total_Rows_Compiled = Worksheets("Compiled").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("Compiled").Range("$A$1:$G$52818").AutoFilter Field:=1, Criteria1:=Worksheets("Sheet1").Cells(l, 1)
Worksheets("Compiled").Range("A2:G" & Total_Rows_Compiled).SpecialCells(xlCellTypeVisible).Select

Upvotes: 2

Views: 145

Answers (2)

CallumDA
CallumDA

Reputation: 12113

You could loop through the Areas:

    Dim filterRange As Range, filteredRange As Range, area As Range

    Set filterRange = Worksheets("Compiled").Range("$A$1:$G$52818")
    filterRange.AutoFilter Field:=1, Criteria1:=Worksheets("Sheet1").Cells(l, 1)

    Set filteredRange = Intersect(filterRange, filterRange.Offset(1, 0)).SpecialCells(xlCellTypeVisible) 'remove headers

    If Not filteredRange Is Nothing Then
        For Each area In filteredRange.Areas
            'do something with area
            Debug.Print area.Address
        Next area
    End If

The following data, when filtered on "test", returns the ranges (areas) B2:F2 and B4:F5 as required

enter image description here

Upvotes: 2

user4039065
user4039065

Reputation:

There doesn't appear to be anything substantially wrong with your code. With that in mind, the following is a combination of methods that I have found to be reasonably error free.

with Worksheets("Compiled")
    .activate
    if .autofiltermode then .autofiltermode = false
    Total_Rows_Compiled = .Range("A" & .Rows.Count).End(xlUp).Row
    with .range("A1:G" & Total_Rows_Compiled)
        .AutoFilter Field:=1, Criteria1:=Worksheets("Sheet1").Cells(l, 1)
        with .resize(.rows.count-1, .columns.count).offset(1, 0)
            if cbool(application.subtotal(103, .cells)) then
                .SpecialCells(xlCellTypeVisible).Select
            end if
        end with
    end with
end with

Upvotes: 3

Related Questions