Setting AutoFilter to 1 row of data

[Greg Bloom]

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(xlCellTypeVisible)), but I don't
know how access the range properly?

Thanks

 

[Ron de Bruin]

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