another autofilter question

[Gary Keramidas]

i use these to give me a range of filtered data:

fRow = .CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).Row
lRow = .Cells(Rows.Count, "A").End(xlUp).Row
(these seem to always work and give me range of rows)

but when i use the following line to set a range, it doesn't work if there is
only 1 filtered row. it works if there is more than 1 row.
Set rng = ws.Range(ws.Cells(fRow, "I"), ws.Cells(lRow,
"I")).SpecialCells(xlCellTypeVisible)
i get something like this for the range:
$1:$5,$3762:$3762,$8375:$65536
you can see that 3762 is the one filtered row i want in this case.

how can i set the range if there is only 1 row of filtered data?

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim VRng As Range

With ActiveSheet.AutoFilter.Range
If .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then
'nothing but headers are visible
Set VRng = Nothing
Else
Set VRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End If
End With

If VRng Is Nothing Then
MsgBox "nothing but headers"
Else
MsgBox VRng.Address & vbLf & _
"Is the address of the visible cells in the first column" & vbLf & _
VRng.EntireRow.Address
End If

End Sub

 

[Gary Keramidas]

thanks dave, i'll take a look.

--


Gary

Option Explicit
Sub testme()

Dim VRng As Range

With ActiveSheet.AutoFilter.Range
If .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then
'nothing but headers are visible
Set VRng = Nothing
Else
Set VRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End If
End With

If VRng Is Nothing Then
MsgBox "nothing but headers"
Else
MsgBox VRng.Address & vbLf & _
"Is the address of the visible cells in the first column" & vbLf & _
VRng.EntireRow.Address
End If

End Sub

 

[Gary Keramidas]

dave:

i think will workout fine. i changed the if statement to the following because i
create a collection for all unique items, so i know there is at least 1 item to
satisfy the filter created from each element of the collection. i just needed to
know when there was only 1 item.

If .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count = 2 Then

thanks.