Filtered range - need to row numbers :/

[theta]

Hi all...I have a filtered range (A1:H50)

When filtered, obviously you get a mix of row numbers (row 10, 15, 17, 24)

I need to be able to Debug.print the address of column 5, for each row in the filtered range e.g.


$E$10
$E$15
$E$17
$E$24


This will allow to modify the code for my purposes. But having real problems with this. The filtered range itself has a name 'rng' and tried each cel in rng.columns(5) and no joy

Any help appreciated... :)

[Bob Phillips]

Try this




For Each cell In rng.Columns("E").SpecialCells(xlCellTypeVisible).Cells

Debug.Print cell.Address
Next cell

[theta]

Hmmm..this prints ALOT of cell, that aren't even in my range.

If I debug.print rng I get :

$A$1:$C$5,$E$1:$E$5,$H$1:$I$5,$P$1:$Q$5

But then trying to perform your action on rng gives me thousands of cell, spanning all columns in the workbook?

[theta]

Sample from debug (which gets overloaded)

$WKY$6
$WKZ$6
$WLA$6
$WLB$6
$WLC$6
$WLD$6

[Bob Phillips]

Only did a max of 50 in my test. Post your workbook to look at.

[theta]

I have resolved it (i think). I have about 4 ranges that I am working with...bit complicated.

If I upload my whole solution (the full workbook + data) would you be able to review the coding and highlight any glaring mistakes?

It is the v1.0 so full of fluff and inefficient code - but it does work ;)

Kept getting error when referencing ranges so kept having the reference worksheets by hand Worksheets("X").Range("Y")

Very simple solution - but alot of mixed up code to get desired result...

[p45cal]

try:Sub blah()
For Each Cll In Intersect(Range("rng"), Columns("E")).SpecialCells(xlCellTypeVisible).Cells
Debug.Print Cll.Address
Next Cll
End Sub


Now this:

If I debug.print rng I get :
$A$1:$C$5,$E$1:$E$5,$H$1:$I$5,$P$1:$Q$5I don't understand; how have you set/assigned rng?

[theta]

:) another sterling contribution. I have not used the insersect method before. Thanks for your help