Setting AutoFilter to 1 row of data
 

I want to set an AutoFilter that returns 1 row of data (which works). I now
want to assign the value from each column of the 1 row data range to a label
on a User Form. I have tried to set a range (Set rgSales = Range("a1",
Range("n65536").End(xlUp)).SpecialCells(xlCellType Visible)), but I don't
know how access the range properly?

Thanks

Setting AutoFilter to 1 row of data
 

Try this example
posted by Dave Peterson

Sub GetFirstRow()
'posted by Dave Peterson
Dim curWks As Worksheet
Dim rng As Range
Dim rngF As Range

Set curWks = ActiveSheet

With curWks
If Not .AutoFilterMode Then
MsgBox "Please apply a filter"
Exit Sub
End If

If Not .FilterMode Then
MsgBox "you haven't filtered anything"
Exit Sub
End If

Set rng = .AutoFilter.Range

Set rngF = Nothing
On Error Resume Next
With rng
'just first column of filtered range
Set rngF = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
End With
On Error GoTo 0

If rngF Is Nothing Then
MsgBox "Filter showed nothing"
Else
.Cells(rngF(1).Row, 1).Select
End If

End With
End Sub




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Greg Bloom" wrote in message news:ea2db.590841$YN5.431525@sccrnsc01...
I want to set an AutoFilter that returns 1 row of data (which works). I now
want to assign the value from each column of the 1 row data range to a label
on a User Form. I have tried to set a range (Set rgSales = Range("a1",
Range("n65536").End(xlUp)).SpecialCells(xlCellType Visible)), but I don't
know how access the range properly?

Thanks