Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to move to the next filtered row/col in VBA?
Hi
Is there a relatively simple piece of code which will allow me to move down to the 'adjacent' row/column I wish to do this because I have a filtered list, and want to move to the 'next' filtered row, although this may not, in actual fact, be the next row, so the offset property would not help here Eg. Filtered Rows 4 and 10 contain my criteria, so normally if I press the down arrow, it'll jump from row 4 to row 10. How can I achieve this using VBA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to move to the next filtered row/col in VBA?
Hello,
You can use SpecialCells(xlCellTypeVisible). Code: -------------------- Dim rng As Range, c As Range Set rng = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTy peVisible) For Each c In rng.Rows MsgBox c.Row c.Select Next -------------------- --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to move to the next filtered row/col in VBA?
Hi Colo
Thanks for your tip. Would there be a way of not selecting the 1st row of the filter For example Row 1 contains my column headings, and row 2 onwards is the actual data. The filter dropdown button is located in row 1. Now, if row 4 and 10 has been filtered out, can I then select the data in row 4 and 10, but not the headings in row 1? Currently the code below selects row 1 in addition to 4 and 10 Thanks very much in advance Code ------------------- Dim rng As Range, c As Rang Set rng = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTy peVisible For Each c In rng.Row MsgBox c.Ro c.Selec Nex -------------------- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to move to the next filtered row/col in VBA?
Hi ~~,
Yes it can be done with offset and resize propertis! ;) Code ------------------- Dim rng As Range, c As Range With ActiveSheet.AutoFilter.Range Set rng = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible) End With For Each c In rng.Rows MsgBox c.Row c.Select Next ------------------- -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return filtered values into report worksheet based on filtered valueon the data worksheet | Excel Worksheet Functions | |||
Excel arrows don't move black box but move the window | Excel Discussion (Misc queries) | |||
How do I stop making the spreadsht move when I move up/dwn/lt/rt? | Excel Worksheet Functions | |||
Be able to move not delete filtered records, to see the duplicats | Excel Worksheet Functions | |||
Move to displayed row of filtered list | Excel Programming |