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

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" wrote in message
...
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

Hello Stefi,

AS the For Each loop finds the visible cells, store the information i
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 Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=47971

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" wrote in message
...
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

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" wrote in message
...
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

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