Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Navigating a filtered list
I have a tonne of data with 4 key columns;
Section, Code, Date and Time I sort the data (Code, Date, Time) and then filter it so I'm left with one Section. Since the first Row is always blank (and I need it to be), how do I do the following: Select the SECOND visible Cell in Column A? (the first being a blank row) Select the NEXT visible Cell in Colun A? Let's say I want to keep the Second visible cell in Column A as the selected cell. How do I do the following? Get data in Column D (same row as the selected cell)? Get data in Column D (next visible row from selected cell)? Hafeez Esmail P.S. This has been posted before but my question wasn't quite answered correctly |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Navigating a filtered list
You can use the SpecialCells method to find the visible rows in an
autofilter. Maybe you can build something based on this example: Sub a() Dim Cell As Range Dim Counter As Long For Each Cell In Range("_FilterDatabase") _ .Columns(1).SpecialCells(xlCellTypeVisible) Counter = Counter + 1 If Counter 2 Then MsgBox Cell.Address Next End Sub Excel always assigns the "_FilterDatabase" range name to a autofilter list. The " 2" is to skip (1) the headings and (2) your blank row. -- Jim Rech Excel MVP |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Navigating a filtered list
Hi Jim, thanks for replying!
I added my code to yours but it doesn't give me what I want. Dim Cell As Range Dim Counter As Long For Each Cell In Range("_FilterDatabase").Columns(1) _ .SpecialCells(xlCellTypeVisible) ..SpecialCells(xlCellTypeVisible) Counter = Counter + 1 If Counter 2 Then MsgBox Str(Counter), , "For Each" Cell(Counter, 1).Select dateA = ActiveCell.Value dateB = ActiveCell.Offset(0, 1).Value strDataD = ActiveCell.Offset(0, 3).Value strDataN = ActiveCell.Offset(0, 13).Value MsgBox "A/B = " & dateA & "/" & dateB End If Next There are two things wrong. 1) it's going to every other cell 2) it's picking up data from every single cell (even ones that are not visible) Please help Hafeez Esmail -----Original Message----- You can use the SpecialCells method to find the visible rows in an autofilter. Maybe you can build something based on this example: Sub a() Dim Cell As Range Dim Counter As Long For Each Cell In Range("_FilterDatabase") _ .Columns(1).SpecialCells(xlCellTypeVisible) Counter = Counter + 1 If Counter 2 Then MsgBox Cell.Address Next End Sub Excel always assigns the "_FilterDatabase" range name to a autofilter list. The " 2" is to skip (1) the headings and (2) your blank row. -- Jim Rech Excel MVP . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Navigating a filtered list
If you look at my code you'll see I do not do any "select"s or
"ActiveCell"s. My code works because the range "Cell" increments through the range. That's what a For Each does. The pros do not use "Activecell" unless they have to. This should run untouched with your filtered list. Try to understand it and come back if you have any questions. Sub a() Dim Cell As Range Dim Counter As Long Dim dateA As Variant Dim dateB As Variant For Each Cell In Range("_FilterDatabase").Columns(1) _ .SpecialCells(xlCellTypeVisible) Counter = Counter + 1 If Counter 2 Then dateA = Cell.Value dateB = Cell.Offset(0, 1).Value MsgBox "A/B = " & dateA & "/" & dateB End If Next End Sub -- Jim Rech Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A list of what is being filtered on? | Excel Discussion (Misc queries) | |||
charting a filtered list | Excel Discussion (Misc queries) | |||
Return Value from a Filtered List | Excel Worksheet Functions | |||
Navigating in filtered rows | Excel Discussion (Misc queries) | |||
Counting a Filtered List | Excel Discussion (Misc queries) |