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?



--


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 wrote:
>
> 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?
>
> --
>
> Gary

--

Dave Peterson

thanks dave, i'll take a look.

--


Gary


"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 wrote:
>>
>> 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?
>>
>> --
>>
>> Gary
>
> --
>
> Dave Peterson

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.

--


Gary


"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 wrote:
>>
>> 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?
>>
>> --
>>
>> Gary
>
> --
>
> Dave Peterson