Sub a()
MsgBox VisibleCells(ActiveSheet).Address(0, 0)
End Sub
'---------------------------------------------------------------------
'Remplace Cells.SpecialCells(xlCellTypeVisible) quand feuille protégée
'---------------------------------------------------------------------
Function VisibleCells(Worksheet As Worksheet, Optional UsedRangeOnly As Boolean = False) As Range
Dim VisibleRows As Range, VisibleColumns As Range
Dim RowColHidden As Boolean
Dim First As Long
Dim Last 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 are always inside the Worksheet.UsedRange (except for the 2 cases above and when hidden Rows are up to the last Row)
With Worksheet.UsedRange
'----------
'Check Rows
'----------
RowColHidden = False
First = 1
Last = .Rows.Count
For i = First To Last
If .Rows(i).Hidden Then
If Not RowColHidden Then
RowColHidden = True
If i > First Then Set VisibleRows = Fusion(VisibleRows, .Rows(First).Resize(i - First).EntireRow)
End If
Else
If RowColHidden Then
RowColHidden = False
First = i
End If
End If
Next i
If Not RowColHidden Then Set VisibleRows = Fusion(VisibleRows, .Rows(First).Resize(i - First).EntireRow)
If Not UsedRangeOnly Then
'Add non-UsedRange above Range
If Not .Rows(1).Top = 0 Then
If .Row > 1 Then
Set VisibleRows = Fusion(Worksheet.Rows(1).Resize(.Row - 1), VisibleRows)
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 VisibleRows = Fusion(Worksheet.Rows(.Row + .Rows.Count).Resize(Rows.Count - (.Row + .Rows.Count - 1)), VisibleRows)
End If
End If
End If
End With
With Worksheet
'-------------
'Check Columns
'-------------
RowColHidden = False
First = IIf(UsedRangeOnly, .UsedRange.Column, 1)
Last = IIf(UsedRangeOnly, .UsedRange.Column + .UsedRange.Columns.Count - 1, .Columns.Count)
For i = First To Last
If .Columns(i).Hidden Then
If Not RowColHidden Then
RowColHidden = True
If i > First Then Set VisibleColumns = Fusion(VisibleColumns, .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 VisibleColumns = Fusion(VisibleColumns, .Columns(First).Resize(, i - First))
End With
'------------
'Return value
'------------
Set VisibleCells = Intersect(VisibleRows, VisibleColumns)
End Function
Private Function Fusion(Range1 As Range, Range2 As Range) As Range
Select Case True
Case Range1 Is Nothing And Range2 Is Nothing
Set Fusion = Nothing
Case Range1 Is Nothing
Set Fusion = Range2
Case Range2 Is Nothing
Set Fusion = Range1
Case Else
Set Fusion = Union(Range1, Range2)
End Select
End Function
