Microsoft 365 Utilisation d'un Spinbutton down sur plage filtrée

[Skyna]

Bonjour,
Je souhaiterais utiliser un spinbutton sur une plage filtrée. Je parviens à le faire en un avec le code suivant :
With ActiveCell
With .Offset(1, 0).Resize(Rows.Count - .Row, 1)
.SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
End With
End With

mais je ne parviens pas à le faire fonctionner en mode filtre avec le down :
With ActiveCell
With .Offset(-1, 0).Resize(Rows.Count - .Row, 1)
.SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
End With
End With

En double cliquant sur une cellule du tableau (du classeur joint) le userform apparaît.

Auriez-vous une solution svp ?

Merci

 

[mapomme]

Bonsoir @Skyna ,

Voir le fichier joint.

Private Sub SpinButton1_SpinDown()
Dim haut&, col&, depart&, i&
   With Sheets("Feuil1")
      haut = .ListObjects("Tableau1").DataBodyRange.Row
      col = ActiveCell.Column
      depart = ActiveCell.Row - 1
      For i = depart To haut Step -1
         If Rows(i).Hidden = False Then Cells(i, col).Select: Exit Sub
      Next i
   End With
End Sub

Private Sub SpinButton1_Spinup()
Dim bas&, col&, depart&, i&
   With Sheets("Feuil1")
      bas = .ListObjects("Tableau1").DataBodyRange.Row + .ListObjects("Tableau1").DataBodyRange.Rows.Count - 1
      col = ActiveCell.Column
      depart = ActiveCell.Row + 1
      For i = depart To bas Step 1: If Rows(i).Hidden = False Then Cells(i, col).Select: Exit Sub
      Next i
   End With
End Sub

 

[laurent950]

Bonsoir,

Selon le Poste #1

Option Explicit
Private Sub SpinButton1_SpinDown()
Dim Rgn, Plg, T As Range
    Set Rgn = ActiveCell
    If Rgn.Row > CLng(2) Then
        With Rgn
            Set Plg = .Offset(-.Row + 3, 0).Resize(.Row, 1)
                With Plg
                    Set T = .SpecialCells(xlCellTypeVisible)
                        If T.Areas.Count > 1 Then Cells(T.Areas.Item(T.Areas.Count - 1).Row, .Column).Select
                End With
        End With
    End If
    Set Rgn = Nothing: Set Plg = Nothing: Set T = Nothing
End Sub
' -----------------------------------------------------------------------------------'
Private Sub SpinButton1_SpinUp()
Dim Rgn, Plg, T As Range
    Set Rgn = ActiveCell
    If Rgn.Row < CLng(2) + Cells(65536, 2).End(xlUp).Row Then
        With Rgn
            Set Plg = .Resize(Cells(65536, 2).End(xlUp).Row - .Row, 1)
                With Plg
                    Set T = .SpecialCells(xlCellTypeVisible)
                        If T.Areas.Count > 1 Then Cells(T.Areas.Item(2).Row, .Column).Select
                End With
        End With
    End If
    Set Rgn = Nothing: Set Plg = Nothing: Set T = Nothing
End Sub

 

[mapomme]

Re,

Une version plus "élégante" :

Private Sub SpinButton1_SpinDown()
   HautBas -1
End Sub

Private Sub SpinButton1_Spinup()
   HautBas 1
End Sub

Sub HautBas(sens&)
Dim bas&, col&, borne1&, borne2&, i&
   With Sheets("Feuil1")
      If .ListObjects("Tableau1").DataBodyRange Is Nothing Then Exit Sub
      borne1 = ActiveCell.Row + sens
      borne2 = IIf(sens = -1, .ListObjects("Tableau1").DataBodyRange.Row, .ListObjects("Tableau1").DataBodyRange.Row + .ListObjects("Tableau1").DataBodyRange.Rows.Count - 1)
      col = ActiveCell.Column
      For i = borne1 To borne2 Step sens
         If Rows(i).Hidden = False Then Cells(i, col).Select: Exit Sub
      Next i
   End With
End Sub

 

[Skyna]

Merci beaucoup à vous 2! C'est parfait !!

 

[laurent950]

Bonjour,
Poste #3 en 1 Ligne de code

Option Explicit
Private Sub SpinButton1_SpinDown()
    If ActiveCell.Offset(-ActiveCell.Row + 3, 0).Resize(ActiveCell.Row, 1).SpecialCells(xlCellTypeVisible).Areas.Count > 1 Then Cells(ActiveCell.Offset(-ActiveCell.Row + 3, 0).Resize(ActiveCell.Row, 1).SpecialCells(xlCellTypeVisible).Areas.Item(ActiveCell.Offset(-ActiveCell.Row + 3, 0).Resize(ActiveCell.Row, 1).SpecialCells(xlCellTypeVisible).Areas.Count - 1).Row, ActiveCell.Offset(-ActiveCell.Row + 3, 0).Resize(ActiveCell.Row, 1).Column).Select
End Sub
' -----------------------------------------------------------------------------------'
Private Sub SpinButton1_SpinUp()
    If ActiveCell.Resize(Cells(65536, 2).End(xlUp).Row - ActiveCell.Row, 1).SpecialCells(xlCellTypeVisible).Areas.Count > 1 Then Cells(ActiveCell.Resize(Cells(65536, 2).End(xlUp).Row - ActiveCell.Row, 1).SpecialCells(xlCellTypeVisible).Areas.Item(2).Row, ActiveCell.Column).Select
End Sub