activecell

[slikity]

After filtering in VBA, how do you make the first
filtered cell active?

 

[Norman Jones]

Hi Slikity,

Try

Sub Tester03()
Dim rng As Range

Set rng = ActiveSheet.AutoFilter.Range
Set rng = Range(rng.Offset(1, 0), rng(rng.Count))
On Error Resume Next
Set rng = rng.SpecialCells(xlVisible)
On Error GoTo 0
If Not rng Is Nothing Then
rng(1).Select
End If
End Sub

 

[Dave Peterson]

This worked ok for me:

Option Explicit

Sub Tester03()
Dim rng As Range

With ActiveSheet.AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
MsgBox "no visible cells"
Else
.Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible).Cells(1).Select
End If
End With
End Sub

 

[Dave Peterson]

If no rows were visible after the filter, then this selected the cell in the row
below the autofilter range.

This line:
Set rng = Range(rng.Offset(1, 0), rng(rng.Count))
actually just offset the range by one row. I bet you wanted something like:

Set rng = rng.Resize(rng.Rows.Count - 1).Offset(1, 0)

But using a single variable causes some trouble too.

Set rng = ActiveSheet.AutoFilter.Range
Set rng = rng.Resize(rng.Rows.Count - 1).Offset(1, 0)
On Error Resume Next
Set rng = rng.SpecialCells(xlVisible)
On Error GoTo 0

If the rng.specialcells(xlvisible) causes an error, rng isn't set to Nothing.
It doesn't change from what it was before.

It might be easier to use another variable, kind of:

Sub Tester03b()

Dim rng As Range
Dim rngF As Range

Set rng = ActiveSheet.AutoFilter.Range
Set rng = rng.Resize(rng.Rows.Count - 1).Offset(1, 0)

Set rngF = Nothing
On Error Resume Next
Set rngF = rng.SpecialCells(xlVisible)
On Error GoTo 0

If Not rngF Is Nothing Then
rngF(1).Select
End If

End Sub

 

[Norman Jones]

Hi Dave,

Thank you and :yes, yes and yes.

As you may have suspected, lazily adopting the path of least
resistance,rather than writing from scratch, I adapted a macro that I
frequently use for autofilter navigation. With my brain in neutral, I
merged my Rng and Rng1 variables and failed to adjust the offset. Of course
limited testing produced no discrepancies. On balance a shoddy effort!

 

[Dave Peterson]

I just try to slough(?) it off to typos <vbg>.

Hi Dave,

Thank you and :yes, yes and yes.

As you may have suspected, lazily adopting the path of least
resistance,rather than writing from scratch, I adapted a macro that I
frequently use for autofilter navigation. With my brain in neutral, I
merged my Rng and Rng1 variables and failed to adjust the offset. Of course
limited testing produced no discrepancies. On balance a shoddy effort!

 

[Guest]

WHat change do I have to do to the code in order for it to select each row
visible til the end of the shown rows?
thanks
Pedro

 

[KL]

Pedro,

I am not sure I completely understood your question, but maybe this is what
you are looking for:

With ActiveSheet.AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
MsgBox "no visible cells"
Else
.Cells.SpecialCells(xlCellTypeVisible).Select
End If
End With

Regards,
KL