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

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

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


.