Pourquoi un fichier peut-il soudainement devenir obèse et que faire pour le mettre au régime ?

Excel a la méchante habitude de conserver en mémoire comme référence de plage de cellules utilisée, la cellule A1 et la cellule la plus éloignée dans laquelle tu as pu placer à un moment donné une information, un format ou une formule. Même si par la suite tu as effacé ces données, excel se souvient que tu as utilisé cette cellule.

Une cellule qui est vide mais qui a été formatée "compte" comme une cellule remplie.

Par exemple, si tu sélectionnes une colonne entière et que tu appliques une couleur de fond, ou une mise en gras de la police, la plage de données utilisées pour excel va brusquement aller jusqu'à la cellule 65536... même si tu n'as que 3 cellules utiles dans cette colonne. Avec excel, comme dans les autres logiciels, le contenu des cellules, les formules ou un module de code (macros), ne prennent pas beaucoup d'espace mémoire, mais la taille de la plage de cellules que tu utilises dans chacune des feuilles en requiert beaucoup. Comme excel conserve en mémoire les lignes et les colonnes qui ne servent plus à rien tu peux te retrouver avec un gros fichier même si le contenu est très limité.

Lorsque tu élabores ton classeur, si tu veux faire des tests c'est une très mauvaise idée de les faire en bas ou à droite de ta zone de travail. Lorsque tu effaces les données de ton test, tu pourrais oublier quelques cellules qui ont par exemple seulement un espace, créé par la barre d'espacement ou une formule et donc non visible à l'oeil. Outre que cela peut faire prendre de l'embonpoint à ton classeur, cela pourrait aussi être suffisant parfois rendre certaines macros ou fonctions inopérantes. Il n'est pas facile par la suite de trouver la causes de telles erreurs.

Pour savoir quelle est la plage considérée comme "utilisée" par excel, appuie sur ctrl+fin. Si le curseur se retrouve en bas alors que rien ne s'y trouve, tu as identifié ton problème. La solution c'est d'abord de ne jamais appliquer un format à toute une ligne ou colonne.

Pour éliminer les formats inutiles qui s'y trouvent tu peux appliquer la macro adhoc sur la page formats. Ensuite sélectionne les lignes et colonnes inutiles et supprime les. Attention, il ne faut pas les effacer mais bel et bien les supprimer (édition/supprimer). Comme excel fera toujours en sorte d'avoir 256 colonnes et 65536 lignes dans une feuille, il va recréer des cellules vides pour compléter la feuille ce qui fait que visuellement on peut avoir l'impression qu'il ne s'est rien passé.Cette manoeuvre peut être faite manuellement ou en utilisant l'une des macros ci-dessous.

sub RéinitUsedRange()
   ' réinitialise l'emplacement de la dernière cellule
 ActiveSheet.UsedRange
 end sub

Et pour faire une cure contrex, ces trois procédures sont très efficaces : Tu peux les mettre dans le perso.xls

Sub Reconstruit(NomClasseur$) 'Frédéric Sigonneau, MPFE
'le projet du classeur ne doit pas être protégé
Dim Wbk As Workbook, Chemin$, tmpNom$, Nom$
Dim Projet, i&, Module$

On Error Resume Next
Set Wbk = Workbooks(NomClasseur)
On Error GoTo 0
If Wbk Is Nothing Then
MsgBox "Le classeur à reconstruire doit être ouvert..."
Exit Sub
End If

'dossier temporaire pour l'exportation des modules de code
Chemin = Wbk.Path & "\empExport"
MkDir Chemin: Chemin = Chemin & "\"

'export des modules
Set Projet = Wbk.VBProject
With Projet
For i = 1 To .VBComponents.Count
Select Case .VBComponents(i).Type
Case 1:
.VBComponents(i).Export Chemin & .VBComponents(i).Name & ".bas"
Case 2:
.VBComponents(i).Export Chemin & .VBComponents(i).Name & ".cls"
Case 3:
.VBComponents(i).Export Chemin & .VBComponents(i).Name & ".frm"
End Select
Next
End With

'export des feuilles dans un nouveau classeur
tmpNom = Left(NomClasseur, Len(NomClasseur) - 4) & "_Refait.xls"
Wbk.Sheets.Copy
ActiveWorkbook.SaveAs Wbk.Path & "\" & tmpNom

'réimport des modules dans le nouveau classeur
Module = Dir(Chemin & "*.*")
Do While (Len(Module) > 0)
On Error Resume Next
Workbooks(tmpNom).VBProject.VBComponents _
.Import(Chemin & Module).Name = Module
On Error GoTo 0
Kill Chemin & Module
Module = Dir()
Loop

'enregistrement et nettoyage
Workbooks(tmpNom).Close True
RmDir Chemin

'remplacement de l'ancien fichier par le nouveau
If MsgBox("Donner au fichier reconstruit le nom du fichier " & _
"d'origine et détruire ce dernier ?", vbYesNo) = vbYes Then
Chemin = Wbk.Path & "\": Nom = Wbk.Name
Wbk.Close False
Kill Chemin & Nom
Name Chemin & tmpNom As Chemin & Nom
End If

End Sub

Sub Nettoie() 'Laurent Longre mpfe, mise en forme GeeDee
Dim Sht As Worksheet, DCell As Range, Calc As Long, Rien As String,
Avant As Double, plage As Range
On Error Resume Next
Calc = Application.Calculation ' ---- mémorisation de l'état de recalcul
'------------------------------------------------------------
MsgBox "Pour le classeur actif  : " _
& Chr(10) & ActiveWorkbook.FullName _
& Chr(10) & "dans chaque feuille de calcul" _
& Chr(10) & "recherche la zone contenant des données," _
& Chr(10) & "réinitialise la dernière cellule utilisée" _
& Chr(10) & "et optimise la taille du fichier Excel", _
vbInformation, _
"d'après LL par GeeDee@m6net.fr"
'-------------------------------------------------------------
MsgBox "Taille initiale de ce classeur en octets" _
& Chr(10) & FileLen(ActiveWorkbook.FullName), _
vbInformation, ActiveWorkbook.FullName
'------------------------------------------------------------
With Application
.Calculation = xlCalculationManual
.StatusBar = "Nettoyage en cours..."
.EnableCancelKey = xlErrorHandler
.ScreenUpdating = True
End With
'-------------------- le traitement
For Each Sht In Worksheets
Avant = Sht.UsedRange.Cells.Count
Application.StatusBar = Sht.Name & "-" & Sht.UsedRange.Address
'-------------------Traitement de la zone trouvée
If Sht.UsedRange.Address <> "$A$1" Or Not IsEmpty(Sht.[A1]) Then
Set DCell = Sht.Cells.Find(What:="*", _
                   LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
         SearchDirection:=xlPrevious)(2)
'----------------Suppression des lignes inutilisées
If Not DCell Is Nothing Then
Sht.Range(DCell, Sht.Cells([A:A].Count, 1)).EntireRow.Delete
Set DCell = Nothing
Set DCell = Sht.Cells.Find(What:="*", _
                   LookIn:=xlFormulas, _
                SearchOrder:=xlByColumns, _
         SearchDirection:=xlPrevious)(2)
'----------------Suppression des colonnes inutilisées
If Not DCell Is Nothing Then Sht.Range(DCell, Sht.[IV1]).EntireColumn.Delete
End If
Rien = Sht.UsedRange.Address
End If
ActiveWorkbook.Save
'---------------------Message pour la feuille traitée
MsgBox "Nom de la feuille de calcul :" _
& Chr(10) & Sht.Name _
& Chr(10) & Format(Sht.UsedRange.Cells.Count / Avant, "0.00%") &  _
" de la taille initiale", vbInformation, ActiveWorkbook.FullName
Next Sht
'--------------------Message fin de traitement
MsgBox "Taille optimisée de ce classeur en octets " & Chr(10) & _
FileLen(ActiveWorkbook.FullName), _
vbInformation, _
ActiveWorkbook.FullNameActive
'--------------------
Application.StatusBar = False
Application.Calculation = Calc
End Sub

Sub ménage()
'Jacques Chaussard et Denis Michon
Dim sh As WorkSheet
On Error Resume Next
For Each sh In Worksheets

sh.Range(sh.Cells.SpecialCells( _
xlCellTypeLastCell).EntireRow, _
sh.Cells.Find("*", , xlFormulas, _
, xlByRows, xlPrevious).EntireRow) _
.Offset(1, 0).Delete

sh.Range(sh.Cells.SpecialCells( _
xlCellTypeLastCell).EntireColumn, _
sh.Cells.Find("*", , xlFormulas, _
, xlByColumns, xlPrevious). _
EntireColumn).Offset(0, 1).Delete
Next
ActiveWorkbook.Save
Set Sh = Nothing
End Sub