Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter
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. I'm now left with a range. 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? 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)? Any help would be appreciated Hafeez Esmail |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter
I have a macro (given to me by a caring MVP!) that increments down through
the visible cells after filtering. Every time I need to do something and then move down one, I set my actins and then call this macro. The code is at the end of this post. You may have to play with it a bit for your system. To select the cell in same row Column D, I would capture the row number, then select the cell. strThisRow = ActiveCell.Row ' assumes you have Dim strThisRow As String Range("D" & strThisRow).Select To move down from there, I would call my macro. Sub Increment1() Dim rng As Range, rng1 As Range Dim icol As Long icol = ActiveCell.Column Set rng = ActiveSheet.AutoFilter.Range Set rng = Intersect(rng, Columns(icol)) Set rng = Range(ActiveCell.Offset(1, 0), rng(rng.Count)) On Error Resume Next Set rng1 = rng.SpecialCells(xlVisible) On Error GoTo 0 If Not rng Is Nothing Then rng1(1).Select End If End Sub There's probably a better way, but I'm very inexperienced and this is how I work. HTH Ed "Hafeez Esmail" wrote in message ... 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. I'm now left with a range. 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? 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)? Any help would be appreciated Hafeez Esmail |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter
I appreciate the help. I know now that I should approach
it by defining a new range but with that, I still don't know how to get information: 1) By selecting the a visible cell 2) By selecting the next visible cell 3) By not selecting a cell The problem is, that I don't know if my current cell is visible or not and I don't want the code to loop through 20 000 cells to see if it's visible or not. Thanks for the help Ed -----Original Message----- I have a macro (given to me by a caring MVP!) that increments down through the visible cells after filtering. Every time I need to do something and then move down one, I set my actins and then call this macro. The code is at the end of this post. You may have to play with it a bit for your system. To select the cell in same row Column D, I would capture the row number, then select the cell. strThisRow = ActiveCell.Row ' assumes you have Dim strThisRow As String Range("D" & strThisRow).Select To move down from there, I would call my macro. Sub Increment1() Dim rng As Range, rng1 As Range Dim icol As Long icol = ActiveCell.Column Set rng = ActiveSheet.AutoFilter.Range Set rng = Intersect(rng, Columns(icol)) Set rng = Range(ActiveCell.Offset(1, 0), rng(rng.Count)) On Error Resume Next Set rng1 = rng.SpecialCells(xlVisible) On Error GoTo 0 If Not rng Is Nothing Then rng1(1).Select End If End Sub There's probably a better way, but I'm very inexperienced and this is how I work. HTH Ed "Hafeez Esmail" wrote in message ... 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. I'm now left with a range. 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? 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)? Any help would be appreciated Hafeez Esmail . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2007 autofilter change to 2003 autofilter functionality? | Excel Discussion (Misc queries) | |||
2007 excel autofilter back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
2007 excel autofilter change back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
2007 Autofilter worse than 2003 Autofilter | Excel Discussion (Misc queries) | |||
How to Sort within AutoFilter with Protection on (and AutoFilter . | Excel Discussion (Misc queries) |