cell values from filtered rows in excel using vba

Question

I have a excel file where I have used the filter on a specific column. After that it returned me 3 visible rows. Now I want to extract a cell value from visible 3 rows on same column. How to write the vba code for that.

Note: I am using UFT, vb script for connecting excel application.

Environment.value("Path1")="C:Test\Data1\"
Environment.value("FileName")="ExcelTest.xlsx"
Set obj = CreateObject("Excel.Application")
obj.visible=True  
Set obj1 = obj.Workbooks.Open(Environment("Path1")&Environment("FileName"))
Set obj2=obj1.Worksheets("RESULT")
obj2.Range("L1").Autofilter 12,"abcdef"
obj2.Range("A1").Autofilter 1,Array("Bucket",2,"Material","Flags"),7
rows=obj2.usedrange.columns(1).specialcells(12).count-1

Lambik Lambik · Accepted Answer · 2018-11-12T11:52:55

if you want to work with visible cells only.

An example in which you filter on column A, to be adapted for you data, of course:

    Sub test()


Dim ws As Worksheet
Dim i As Long, LastRow As Long
Dim r As Range, Cell As Range, Range As Range

Set ws = ThisWorkbook.Sheets("Sheet1")
Set r = ws.Range("A1")

 ws.AutoFilterMode = False
    With r
        .AutoFilter Field:=1, Criteria1:="Yourcriteria"
        LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
        Set Range = ws.Range(ws.Cells(2, 1), ws.Cells(LastRow, 1))

        For Each Cell In Range.SpecialCells(xlCellTypeVisible)
            'whatever you need to be done
        Next Cell

    End With
    ws.AutoFilterMode = False  
End Sub

cell values from filtered rows in excel using vba

1 Answers