Actual row of a filtered row
Hello Group,
I am using the auto filter and when I filter the list I would like to get the actual (blue) rows of the filtered data . Using .row I get the sequential row counting from row 1. Thanks, Michael Singmin |
Actual row of a filtered row
if you want to copy them, then the default is to copy only the visible rows
ActiveSheet.Autofilter.Range.Copy Destination:=Activesheet.Next.Range("A1") If you want a reference to them set rng = Activesheet.Autofilter.Range.Columns(1).Cells set rng = rng.offset(1,0).Resize(rng.rows.count-1) set rng1 = rng.Specialcells(xlVisible) if not rng1 is nothing then msgbox rng1.Entirerow.Address End if -- Regards, Tom Ogilvy "Michael Singmin" wrote in message ... Hello Group, I am using the auto filter and when I filter the list I would like to get the actual (blue) rows of the filtered data . Using .row I get the sequential row counting from row 1. Thanks, Michael Singmin |
Actual row of a filtered row
Thank you Tom,
Your code is always interesting to look at. But I was looking for this For Each fs In Range("A1:A500").SpecialCells(xlCellTypeVisible) Msgbox fs.row Next Cheers, Michael Singmin ================================================== ======= "Tom Ogilvy" wrote: if you want to copy them, then the default is to copy only the visible rows ActiveSheet.Autofilter.Range.Copy Destination:=Activesheet.Next.Range("A1") If you want a reference to them set rng = Activesheet.Autofilter.Range.Columns(1).Cells set rng = rng.offset(1,0).Resize(rng.rows.count-1) set rng1 = rng.Specialcells(xlVisible) if not rng1 is nothing then msgbox rng1.Entirerow.Address End if |
All times are GMT +1. The time now is 10:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com