Setting AutoFilter to 1 row of data

  • Thread starter Thread starter Greg Bloom
  • Start date Start date
G

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
 
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
 
Back
Top