XL 2021 VBA Alternative à SpecialCells(xlCellTypeVisible)

Sub a()
    MsgBox VisibleCells(ActiveSheet).Address(0, 0)
End Sub

'---------------------------------------------------------------------
'Remplace Cells.SpecialCells(xlCellTypeVisible) quand feuille protégée
'---------------------------------------------------------------------
Function VisibleCells(Worksheet As Worksheet) As Range
    Dim Range As Range
    Dim UsedRangeVisibleRows As Range, UsedRangeVisibleColumns As Range
    Dim WorksheetVisibleRows As Range, WorksheetVisibleColumns As Range
    Dim RowColHidden As Boolean
    Dim First As Long
    Dim i As Long
    
    'All Rows hidden
    With Worksheet.Rows(Rows.Count)
        If .Top + .Height = 0 Then Exit Function
    End With
    
    'All Columns hidden
    With Worksheet.Columns(Columns.Count)
        If .Left + .Width = 0 Then Exit Function
    End With
    
    'All hidden Rows & Columns are always inside the Worksheet.UsedRange (except for the 2 cases above)
    With Worksheet.UsedRange
        '----------
        'Check Rows
        '----------
        RowColHidden = False
        First = 1
      
        For i = 1 To .Rows.Count
            If .Rows(i).Hidden Then
                If Not RowColHidden Then
                    RowColHidden = True
                    If i > First Then Set UsedRangeVisibleRows = Réunion(UsedRangeVisibleRows, Worksheet.Rows(First).Resize(i - First))
                End If
            Else
                If RowColHidden Then
                    RowColHidden = False
                    First = i
                End If
            End If
        Next i
        If Not RowColHidden Then Set UsedRangeVisibleRows = Réunion(UsedRangeVisibleRows, Worksheet.Rows(First).Resize(i - First))
        If Not UsedRangeVisibleRows Is Nothing Then Set WorksheetVisibleRows = UsedRangeVisibleRows.Offset(.Row - 1)
      
        'Add non-UsedRange above Range
        If Not .Rows(1).Top = 0 Then
            If .Row > 1 Then
                Set WorksheetVisibleRows = Réunion(Worksheet.Rows(1).Resize(.Row - 1), WorksheetVisibleRows)
            End If
        End If
        
        'Add non-UsedRange below Range
        If Not Round(Worksheet.Rows(Rows.Count).Top + Worksheet.Rows(Rows.Count).Height, 4) = _
               Round(Worksheet.Rows(.Row + .Rows.Count - 1).Top + Worksheet.Rows(.Row + .Rows.Count - 1).Height, 4) Then
            If (.Row + .Rows.Count - 1) < Rows.Count Then
                Set WorksheetVisibleRows = Réunion(Worksheet.Rows(.Row + .Rows.Count).Resize(Rows.Count - (.Row + .Rows.Count - 1)), WorksheetVisibleRows)
            End If
        End If
    End With
      
    With ActiveSheet
        '-------------
        'Check Columns
        '-------------
        RowColHidden = False
        First = 1

        For i = 1 To Columns.Count
            If .Columns(i).Hidden Then
                If Not RowColHidden Then
                    RowColHidden = True
                    If i > First Then Set WorksheetVisibleColumns = Réunion(WorksheetVisibleColumns, Worksheet.Columns(First).Resize(, i - First))
                    'All the remaining columns are hidden ?
                    If .Columns(Columns.Count).Left + .Columns(Columns.Count).Width = .Columns(i).Left + .Columns(i).Width Then Exit For
                End If
            Else
                If RowColHidden Then
                    RowColHidden = False
                    First = i
                End If
            End If
        Next i
        If Not RowColHidden Then Set WorksheetVisibleColumns = Réunion(WorksheetVisibleColumns, Worksheet.Columns(First).Resize(, i - First))
    End With
  
    '------------
    'Return value
    '------------
    Set VisibleCells = Intersect(WorksheetVisibleRows, WorksheetVisibleColumns)
End Function

Private Function Réunion(Range1 As Range, Range2 As Range) As Range
    Select Case True
        Case Range1 Is Nothing And Range2 Is Nothing
            Set Réunion = Nothing
            
        Case Range1 Is Nothing
            Set Réunion = Range2
            
        Case Range2 Is Nothing
            Set Réunion = Range1
            
        Case Else
            Set Réunion = Union(Range1, Range2)
    End Select
End Function

Sub M_Dudu()
     Dim R     As Range, bEAA, bProtected

     bEAA = Application.EnableEvents
     If bEAA Then Application.EnableEvents = False     'éviter lancement des évenements
     With ActiveSheet
          bProtected = .ProtectContents
          If bProtected Then .Unprotect

          On Error Resume Next
          Set R = ActiveSheet.Cells.SpecialCells(xlCellTypeVisible)
          On Error GoTo 0

          If bProtected Then .Protect
     End With
     If bEAA Then Application.EnableEvents = True

     If R Is Nothing Then MsgBox "erreur" Else MsgBox R.Address

End Sub

Function RangeVisibleCells(MyWorksheet As Worksheet) As Range
Dim dercol&, derlig&, i&, j&, Ri As Range, Rj As Range
   With MyWorksheet
         
      derlig = .UsedRange.Row + .UsedRange.Rows.Count - 1
      If derlig = 1 Then
         If .Rows(1).EntireRow.Hidden = True Then Exit Function Else Set Ri = .Rows(1)
      Else
         If derlig + 1 <= Rows.Count Then Set Ri = .Range(.Cells(derlig + 1, 1), .Cells(Rows.Count, 1)).EntireRow
         For i = 1 To derlig
            If Not .Rows(i).Hidden Then
               If Ri Is Nothing Then Set Ri = .Rows(i) Else Set Ri = Union(Ri, .Rows(i))
            End If
         Next i
      End If
      
      dercol = .UsedRange.Column + .UsedRange.Columns.Count - 1
      If dercol = 1 Then
         If .Columns(1).EntireColumn.Hidden = True Then Exit Function Else Set Ri = .Columns(1)
      Else
         If dercol + 1 <= Columns.Count Then Set Rj = .Range(.Cells(1, dercol + 1), .Cells(1, Columns.Count)).EntireColumn
         For j = 1 To dercol
            If Not .Columns(j).Hidden Then
               If Rj Is Nothing Then Set Rj = .Columns(j) Else Set Rj = Union(Rj, .Columns(j))
            End If
         Next j
      End If

     If Ri Is Nothing And Rj Is Nothing Then
         Exit Function
      ElseIf Ri Is Nothing Then
         Set RangeVisibleCells = Rj
      ElseIf Rj Is Nothing Then
         Set RangeVisibleCells = Ri
      Else
         Set RangeVisibleCells = Intersect(Ri, Rj)
      End If
   End With
End Function

     i = Range("A1").End(xlDown).Row
     j = Range("A1").End(xlToRight).Column
     If i + j = 2 Then MsgBox "toutes les lignes et colonnes sont invisibles, (sauf A1 ???)"

Sub Test()
     MsgBox IIf(f_Visible(Sheets("Feuil1").Cells), "une/plusieurs ", "aucune ") & " cellule(s) visible(s)"
End Sub


Function f_Visible(Plage As Range)
     Dim c1 As Range, c2 As Range
     Set c1 = Plage.Find("*", lookat:=xlWhole)
     Set c2 = Plage.Cells.Find("", lookat:=xlWhole)
     f_Visible = Not c1 Is Nothing Or Not c2 Is Nothing
End Function