PDA

View Full Version : Solved: Visible Range after AutoFilter



JKwan
05-11-2009, 12:34 PM
How would I determine the visible range is after executing an Autofilter command? For Instance, after AutoFilter, only rows 200 - 250 are showing.... How do I use a loop to go thru those rows that I want to perform actions on....


For loop = FirstVisibleRow to LastVisibleRow
[do work]
Next


How can I tell that row starts at 200 and end at 250

Just have a thought, what if I only wanted to act on rows that are visible, non-contiguous
Thanks

lucas
05-11-2009, 12:44 PM
set your range to the visable cells maybe:
.SpecialCells(xlCellTypeVisible)

JKwan
05-11-2009, 12:57 PM
Thank you Lucas, this got me started and I think that I am close, I may need another push to get going

Set VisibleRange = ActiveSheet.Cells.SpecialCells(xlCellTypeVisible)


now, when I do a print on VisibleRange.Address, it gives me 3 ranges,
$1:$1 - my header row
$200:$250 - this is what I want to home in onto
$251:$65536 - practically no data afterward

I can parse the $200:$250, now to me this is "static" because that I am picking on the 2nd set of data, what if next time the VisibleRange returns a wack of ranges, how do I determine how many that I should go thru? I hope that I am making sense.....

Thanks.

p45cal
05-11-2009, 02:09 PM
you could try playing withFor rw = 2 To 500
If Not Rows(rw).Hidden Then
MsgBox rw & " is a visible row"
End If
Next rworFor Each rw In Intersect(UsedRange.Columns(1).SpecialCells(xlCellTypeVisible), Range("A2:A500"))
MsgBox rw.Row & " is a visible row"
Next rw

lucas
05-11-2009, 02:17 PM
example of selecting visible cells after filtering attached......I'm sure it will help you understand. It excludes the header row.

JKwan
05-12-2009, 07:04 AM
Thank you all, this solved my problem.