Recentrage automatique d'un tableau

Const celcible = "A1"

Sub recentrage()
Dim derlig As Long, dercol As Long, plage As Range
Dim premlig As Long, premcol As Long
derlig = Cells.Find("*", , , , xlByRows, xlPrevious).Row
premlig = Cells.Find("*", , , , xlByRows, xlNext).Row
dercol = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
premcol = Cells.Find("*", , , , xlByColumns, xlNext).Column
Set plage = Range(Cells(premlig, premcol), Cells(derlig, dercol))
plage.Cut Destination:=Range(celcible)
End Sub

Public Sub Cale()
Dim lideb As Long, codeb As Long, lifin As Long, cofin As Long, li As Long, co As Long
Dim plage As Range
With ActiveSheet
  lideb = .Range(celcible).Row
  codeb = .Range(celcible).Column
  lifin = Cells.Find("*", , , , xlByRows, xlPrevious).Row
  cofin = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
  li = lideb - 1
  Do
    li = li + 1
    co = .Cells(li, Columns.Count).End(xlToLeft).Column
  Loop Until co > codeb
  lideb = li
  co = codeb - 1
  Do
    co = co + 1
    li = .Cells(Rows.Count, co).End(xlUp).Row
  Loop Until li > lideb
  codeb = co
  'MsgBox .Cells(lideb, codeb).Address
  Set plage = .Range(.Cells(lideb, codeb), .Cells(lifin, cofin))
  plage.Cut Destination:=Range(celcible)
End With
End Sub

  lifin = Cells.Find("*", , , , xlByRows, xlPrevious).Row
  cofin = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
  Set plage = .Range(.Cells(1, 1), .Cells(lifin, cofin))
  plage.Select
  ActiveWindow.zoom = True
End With

Option Explicit

Const celcible = "D3"

Sub recentrage_zoom()
Dim derlig As Long, dercol As Long, plage As Range
Dim premlig As Long, premcol As Long, nbli As Long, nbco As Long
' si pas assez grand comme taille, aaugmenter les nb de lignes et de colonnes
Set plage = Range(celcible).Resize(1000, 100)
' MsgBox plage.Address
derlig = plage.Find("*", , , , xlByRows, xlPrevious).Row
premlig = plage.Find("*", , , , xlByRows, xlNext).Row
dercol = plage.Find("*", , , , xlByColumns, xlPrevious).Column
premcol = plage.Find("*", , , , xlByColumns, xlNext).Column
Set plage = Range(Cells(premlig, premcol), Cells(derlig, dercol))
nbli = plage.Rows.Count
nbco = plage.Columns.Count
plage.Cut Destination:=Range(celcible)
derlig = Range(celcible).Row + nbli - 1
dercol = Range(celcible).Column + nbco - 1
premlig = Cells.Find("*", , , , xlByRows, xlNext).Row
premcol = Cells.Find("*", , , , xlByColumns, xlNext).Column
Set plage = Range(Cells(premlig, premcol), Cells(derlig, dercol))
plage.Select
ActiveWindow.zoom = True
Range(celcible).Select
End Sub

plage.Cut Destination:=Range(celcible)
derlig = Range(celcible).Row + nbli - 1
dercol = Range(celcible).Column + nbco - 1
Set plage = Range(Cells(1, 1), Cells(derlig, dercol))
plage.Select
ActiveWindow.zoom = True
Range(celcible).Select
End Sub