Referring to cells in a filtered range

[Stefi]

Hi All,

I created an autofiltered range consisting of one column with
Set filteredrng = entirerng.SpecialCells(xlVisible)
The statement
For each cell in filteredrng
shows correctly the cells in filteredrng.

How can I refer to cells in filteredrng if I do not use For each?
first cell in filteredrng (Row No of, Value of)
second cell in filteredrng (Row No of, Value of)
etc.

Thanks,
Stefi

[Leith Ross]

Hello Stefi,

AS the For Each loop finds the visible cells, store the information in an Array. You can then reference the array later to retrieve the value, address, etc. for the cell you want.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Norman Jones]

Hi Steffi,

> How can I refer to cells in filteredrng if I do not use For each?

I am not aware that this is possible.

To navigate the filtered list, you may wish to use code like the following,
which was provided by Tom Ogilvy:

'================>>
Sub AutoFilterSelectNext()
Dim rng As Range, Rng1 As Range
Dim iCol As Long

iCol = ActiveCell.Column
Set rng = ActiveSheet.AutoFilter.Range
Set rng = Intersect(rng, Columns(iCol))

If rng Is Nothing Then Exit Sub

Set rng = Range(ActiveCell.Offset(1, _
iCol - ActiveCell.Column), rng(rng.Count))

On Error Resume Next
Set Rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0

If Not Rng1 Is Nothing Then
Rng1(1).Select
End If

End Sub
'<<================


--

---
Regards,
Norman



"Stefi" <[email protected]> wrote in message
news:[email protected]...
> Hi All,
>
> I created an autofiltered range consisting of one column with
> Set filteredrng = entirerng.SpecialCells(xlVisible)
> The statement
> For each cell in filteredrng
> shows correctly the cells in filteredrng.
>
> How can I refer to cells in filteredrng if I do not use For each?
> first cell in filteredrng (Row No of, Value of)
> second cell in filteredrng (Row No of, Value of)
> etc.
>
> Thanks,
> Stefi
>

[Stefi]

Thanks Norman,

Tom's code works perfectly as a workaround, but it's surprising that such
obvious features don't exist in VBA!
I tried to use
filteredrng.Item(1).Value
filteredrng.Item(2).Value
etc.
but these gave the same values respectively as
entirerng.Item(1).Value
entirerng.Item(2).Value
etc.

I think this is a misleading behaviour of XL! Of course this is intended not
to you but to Microsoft! The easiest solution seems to be reconstructing the
code for using For each!

Regards,
Stefi


„Norman Jones” ezt �*rta:

> Hi Steffi,
>
> > How can I refer to cells in filteredrng if I do not use For each?
>
> I am not aware that this is possible.
>
> To navigate the filtered list, you may wish to use code like the following,
> which was provided by Tom Ogilvy:
>
> '================>>
> Sub AutoFilterSelectNext()
> Dim rng As Range, Rng1 As Range
> Dim iCol As Long
>
> iCol = ActiveCell.Column
> Set rng = ActiveSheet.AutoFilter.Range
> Set rng = Intersect(rng, Columns(iCol))
>
> If rng Is Nothing Then Exit Sub
>
> Set rng = Range(ActiveCell.Offset(1, _
> iCol - ActiveCell.Column), rng(rng.Count))
>
> On Error Resume Next
> Set Rng1 = rng.SpecialCells(xlVisible)
> On Error GoTo 0
>
> If Not Rng1 Is Nothing Then
> Rng1(1).Select
> End If
>
> End Sub
> '<<================
>
>
> --
>
> ---
> Regards,
> Norman
>
>
>
> "Stefi" <[email protected]> wrote in message
> news:[email protected]...
> > Hi All,
> >
> > I created an autofiltered range consisting of one column with
> > Set filteredrng = entirerng.SpecialCells(xlVisible)
> > The statement
> > For each cell in filteredrng
> > shows correctly the cells in filteredrng.
> >
> > How can I refer to cells in filteredrng if I do not use For each?
> > first cell in filteredrng (Row No of, Value of)
> > second cell in filteredrng (Row No of, Value of)
> > etc.
> >
> > Thanks,
> > Stefi
> >
>
>
>

[Bob Phillips]

You could set a rng to the visible cells, and then iterate through these

Dim cell As Range
Dim rng As Range

Set rng = Columns("B:B").SpecialCells(xlCellTypeVisible)
For Each cell In rng
'...
Next cell


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stefi" <[email protected]> wrote in message
news:[email protected]...
> Hi All,
>
> I created an autofiltered range consisting of one column with
> Set filteredrng = entirerng.SpecialCells(xlVisible)
> The statement
> For each cell in filteredrng
> shows correctly the cells in filteredrng.
>
> How can I refer to cells in filteredrng if I do not use For each?
> first cell in filteredrng (Row No of, Value of)
> second cell in filteredrng (Row No of, Value of)
> etc.
>
> Thanks,
> Stefi
>

[Stefi]

Thanks Leith,

This is a good idea, especially if one often have to re-access the filtered
cells.
But I hold the view that Excel should supply this information itself without
additional coding!

Regards,
Stefi

„Leith Ross” ezt �*rta:

>
> Hello Stefi,
>
> AS the For Each loop finds the visible cells, store the information in
> an Array. You can then reference the array later to retrieve the value,
> address, etc. for the cell you want.
>
>
> Code:
> --------------------
>
> Dim SpCells()
> Dim cell
> Dim n As Long
>
> For Each cell In filteredrng
> Redim Preserve SpCells(1, n)
> SpCells(0, n) = cell.Row
> SpCells(1, n) = cell.Value
> n = n + 1
> Next cell
>
> --------------------
>
>
> Sincerely,
> Leith Ross
>
>
> --
> Leith Ross
> ------------------------------------------------------------------------
> Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
> View this thread: http://www.excelforum.com/showthread...hreadid=479715
>
>