Option Explicit
Sub Avant_dernière_cellule_valeur_connaître()
Application.ScreenUpdating = False
With Columns(1): .Insert: .Offset(, -1).Hidden = True: End With
Columns(2).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants).Copy Range("a1")
MsgBox "Valeur de l'avant-dernière cellule = " & Cells(Rows.Count, "a").End(xlUp).Offset(-1, 0)
Columns(1).Delete: Application.GoTo Reference:=Range("A1"), Scroll:=True
Application.ScreenUpdating = True
End Sub