ExcelBanter - activecell

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - activecell (https://www.excelbanter.com/excel-programming/312608-activecell.html)

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

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

---
Regards,
Norman

"slikity" wrote in message
...
After filtering in VBA, how do you make the first
filtered cell active?

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).Se lect
End If
End With
End Sub

slikity wrote:

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

--

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 wrote:

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

---
Regards,
Norman

"slikity" wrote in message
...
After filtering in VBA, how do you make the first
filtered cell active?

--

Dave Peterson

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!

---
Regards,
Norman

"Dave Peterson" wrote in message
...
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 wrote:

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

---
Regards,
Norman

"slikity" wrote in message
...
After filtering in VBA, how do you make the first
filtered cell active?

--

Dave Peterson

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

"Norman Jones" wrote in message ...
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!

---
Regards,
Norman

"Dave Peterson" wrote in message
...
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 wrote:

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

---
Regards,
Norman

"slikity" wrote in message
...
After filtering in VBA, how do you make the first
filtered cell active?

--

Dave Peterson

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

"Dave Peterson" wrote:

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).Se lect
End If
End With
End Sub

slikity wrote:

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

--

Dave Peterson

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

"Pedro" wrote in message
...
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

"Dave Peterson" wrote:

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).Se lect
End If
End With
End Sub

slikity wrote:

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

--

Dave Peterson