CODEWITHSUNDEEP
vbaexcel
Sandeep Thomas
Sandeep Thomas

Reputation: 4727

Taking Visible range after AutoFilter

Im trying to select filtered rows into a range variable xRange. I need to have some operations on that range. Before filtering there was 10000+ rows. After filtering it is 5000 rows. So after filtering I tried this to get the filtered range which is not working as I expected.

With Sheets("FullInvoice").Range("A1:N" & lRow)
    .AutoFilter
    .AutoFilter Field:=6, Criteria1:=">=" & fD, Criteria2:="<=" & lD

End With
Set xRange = Sheets("FullInvoice").Cells.SpecialCells(xlCellTypeVisible)

I added a break point at Set xRange line. and tried xRange.rows.count. But its just returning 1 even there is 5000 rows. So whats my purpose is to take the cell value of first cell after filtering. How can I accomplish that.

When I try

dim s as string
s=xRange.Range("A1").value

It returning first cell value of the whole rows. Not from the filtered rows.

So how to solve this situation.

Upvotes: 2

Views: 2068

Answers (2)

CallumDA
CallumDA

Reputation: 12113

It's even easier than that. Just bring xrange inside your With. Also I'd use Range.Cells(x,y) rather than Range.Range to get a cell inside a range:

With Sheets("FullInvoice").Range("A1:N" & lRow)
    .AutoFilter
    .AutoFilter Field:=6, Criteria1:=">=" & fD, Criteria2:="<=" & lD
    Set xrange = .SpecialCells(xlCellTypeVisible)
End With

MsgBox xrange.Cells(1, 1)

Upvotes: 2

YasserKhalil
YasserKhalil

Reputation: 9538

Try this code

    With Sheets("FullInvoice").Range("A1:N" & lrow)
    .AutoFilter
    .AutoFilter Field:=6, Criteria1:=">=" & fd, Criteria2:="<=" & ld
End With

With Sheets("FullInvoice").Range("A1").CurrentRegion
    Set xRange = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
End With
MsgBox xRange.Address

Dim s As String
s = xRange.Range("A1").Value
MsgBox s

Upvotes: 1

Related Questions