Sub test()
Set tableau = Range("c3:h25")
MsgBox UsedrangeOnRange(tableau).Address
End Sub
Function UsedrangeOnRange(tableau) As Range
Dim lig&, col&, adresse$, cel1 As Range, cel2 As Range
With tableau
Set cel1 = .Cells(1)
col = .Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
lig = .Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set cel2 = Cells(lig, col)
Set UsedrangeOnRange = .Parent.Range(cel1, cel2)
End With
End Function