CODEWITHSUNDEEP
vbaexcelfilterrange
Mikey
Mikey

Reputation: 70

Excel VBA range after filtering xlCellTypeVisible

What I want to accomplish: Open a workbook from a specific address, Filter the first column for value equal to 36 or 541 (I got this first part working), then check column 3 to see if a value of 2 exists and if it exists then filter out everything except for value 2 in column 3; if value 2 does not exist in column 3 then skip.

I tried using SpecialCells(xlCellTypeVisible) to name the new range but I must be using it incorrectly because it is giving me a value of 2 that exists only in the old range where the data has not been filtered yet.

Thanks for your time!

Sub filters()

Dim wb As Workbook
Dim nwb As Workbook

Set wb = ThisWorkbook

Set nwb = Workbooks.Open("ADDRESS.FILE.xlsx")

With ActiveSheet

.AutoFilterMode = False
.Range("$A$1:$AD$5000").AutoFilter Field:=1, Criteria1:="=36", Operator:=xlOr, Criteria2:="=541"
'.Range("$A$1:$AD$5000").AutoFilter Field:=3, Criteria1:="2"

End With

Dim newrange As Range

Set newrange = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)

Dim i As Integer, intValueToFind As Integer
intValueToFind = 2
For i = 1 To 5000    ' Revise the 5000 to include all of your values
    If newrange(i, 3).Value = intValueToFind Then
        MsgBox ("Found value on row " & i)
        Exit Sub
    End If
Next i

' This MsgBox will only show if the loop completes with no success
MsgBox ("Value not found in the range!")


End Sub

Upvotes: 3

Views: 19248

Answers (1)

Tim Williams
Tim Williams

Reputation: 166221

Something like this should work:

Dim newrange As Range, rw as range

Set newrange = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)

Dim intValueToFind As Integer
intValueToFind = 2
For Each rw in newrange.Rows
    If rw.cells(3).Value = intValueToFind Then
        MsgBox ("Found value on row " & rw.cells(1).Row)
        Exit Sub
    End If
Next rw

Upvotes: 5

Related Questions