View Full Version : Filtered range - need to row numbers :/
theta
02-15-2012, 06:05 AM
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
02-15-2012, 06:54 AM
Try this
For Each cell In rng.Columns("E").SpecialCells(xlCellTypeVisible).Cells
Debug.Print cell.Address
Next cell
theta
02-15-2012, 07:02 AM
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
02-15-2012, 07:04 AM
Sample from debug (which gets overloaded)
$WKY$6
$WKZ$6
$WLA$6
$WLB$6
$WLC$6
$WLD$6
Bob Phillips
02-15-2012, 08:28 AM
Only did a max of 50 in my test. Post your workbook to look at.
theta
02-15-2012, 08:32 AM
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
02-15-2012, 09:36 AM
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
02-15-2012, 09:41 AM
:) another sterling contribution. I have not used the insersect method before. Thanks for your help
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.