[VBA] Macro ANNULER une suppression (par macro) de cellules dans une feuilleRésolu

Question

Bonjour à tous,


Je vous sollicite car j'ai besoin d'une grande aide. En effet, après quelques heures de recherche sur Internet, je n'ai pas encore trouvé de réponses à ma question.


Je souhaite écrire une macro qui annule la suppression de lignes, de colonnes ou de toutes les cellules dans une feuille de calcul. (c'est à dire que la feuille de calcul revienne  à l'état d'avant la suppression, avec les mêmes valeurs et la même mise en forme). Excel le fait facilement, donc cela doit être possible à faire. Le problème est que je n'ai pas accès au code Excel...


J'ai donc bien cerné le fonctionnement de l'application Undo (Application.Undo). Mon idée était de déclarer l'ensemble de mes cellules dans une variable, puis, après suppression, de coller les données (correspondant aux cellules avant suppression) de cette variable dans ma feuille de calcul.


Comme d'habitude, j'utilise une variable "Range" :

Sub UndoSuppression()

Dim RangeSave as Range

Set RangeSave = Cells

End Sub 


Mais la variable "RangeSave" s'adapte à la sélection, c'est à dire que quand, par exemple, je supprime 2 lignes, ces 2 lignes ne sont plus présentes dans "RangeSave". Mon objectif est donc de fixer la plage de données dans une variable, celle-ci restant TOUJOURS la même quelque soit les opérations effectuées (suppression de lignes...).


J'avais aussi pensé à boucler sur l'ensemble des cellules, mais cela est bien sûr ultra long, car il faut gérer plus de 15 millions de cellules !!!



Encore une fois, Excel le fait très simplement et rapidement, donc je pense que cela est largement faisable.



Si vous avez des idées ou des solutions, n'hésitez pas à les faire partager, pour faire avancer les autres ainsi que moi-même.



Merci d'avance et bonne journée.

ucfoutu · Accepted Answer

Non, car rangesave est un tableau de variant.
L'explication est ailleurs.
1) quelle version d'Excel as-tu ? (je viens de tester avec 2007 ===>> impeccable)
2) si le bouton n'est pas sur ta feuille elle-même, il faut la préciser. Ainsi par exemple :

 Dim toto As String, rangesave, lafeuille As Worksheet
 
 Set lafeuille = Sheets("Feuil1")
 With lafeuille
   rangesave = .UsedRange.Value
   RFormule = .UsedRange.Formula
   toto = .UsedRange.Address
   MsgBox toto ' je le laisse pour toi... efface cette instruction ensuite
 
   .Rows("5:15").Delete
   MsgBox "regarde ta feuille"
   Application.ScreenUpdating = False
   .Range(toto).Value = rangesave
   .Range(toto).Formula = RFormule
   Application.ScreenUpdating = True
   MsgBox "regarde ta feuille"
 End With

Marche fort bien chez moi (je viens de tester encore).
J'en ai profité pour traiter également les formules présentes (et les restituer).

____________________
Vous aimez Codes-Sources ? Il vous aide ? Cliquez ici pour l'aider à continuer
Cliquer sur "Réponse acceptée"  en bas d'une solution adéquate est

userrrqi115 · Answer

Hello, 
A mon avis c'est loin d'être facile : lorsque tu effectues des manip sur une feuille Excel manuellement (souris ou raccourcis clavier) Excel te laisse accès à Undo, si tu effectues les mêmes actions via une macro, cet accès n'est pas dispo.
Peut-être qu'en codant les actions de suppression avec des sendkeys (à voir si possible) tu pourrais garder accès à undo??
BR

USERRRQI115
Simple user
Great brain

ucfoutu · Answer

Bonjour,

Pourquoi alors ne pas travailler tout simplement en miroir ?
A l'ouverture de ton classeur ===>> tu en fais une copie ===>> tu ouvres la copie et ferme l'original (qui ne sera donc jamais modifié)


____________________
Vous aimez Codes-Sources ? Il vous aide ? Cliquez ici pour l'aider à continuer
Cliquer sur "Réponse acceptée"  en bas d'une solution adéquate est

Airone1CF03 · Answer

[quote="userrrqi115"]Peut-être qu'en codant les actions de suppression avec des sendkeys (à voir si possible) tu pourrais garder accès à undo??/quote
Peux-tu m'expliquer en quoi cela consiste, car là cela dépasse ma connaissance ?


[quote="ucfoutu"]Pourquoi alors ne pas travailler tout simplement en miroir ?
A l'ouverture de ton classeur ===>> tu en fais une copie ===>> tu ouvres la copie et ferme l'original (qui ne sera donc jamais modifié) /quote
Je n'avais pas tout précisé. En fait, ma macro de suppression, lancée par UserForm, se trouve dans un menu de la barre des menus. L'idée est donc de permettre à l'utilisateur de supprimer X fois N lignes ou colonnes, en lui permettant X fois de revenir à la feuille de calcul avant suppression.

J'avais quelque peu pensé à cette solution, mais cela est un peu rébarbatif, s'il faut sauvegarder le fichier autant de fois que la macro est lancée : ce que je veux en effet, c'est revenir à l'étape précédente et non à l'étape avant ouverture du fichier.



Mon idée est de sauvegarder, non pas le classeur, mais toutes les cellules de la feuille de calcul dans une VARIABLE, afin de pouvoir réattribuer à la feuille de calcul son aspect avant suppression. Et là je ne sais pas faire...


Comme je l'ai dit, j'ai bien tenté de faire un "Set RangeSave = cells", mais après suppression de N lignes, ces mêmes N lignes ont disparu de ma variable Range "RangeSave".



Voilà, si vous pensez pourvoir avancer sur mon idée ou sur d'autres, je vous en remercie d'avance.

ucfoutu · Answer

Si tu ne veux que garder les valeurs, fastoche ! Il tre suffit de les mettre dans un tableau
Exemple :
tablo = Range("A1:C10").Value
 'tu modifies tout ce que tu veux à la main (-ici : on va simuler)
 Range("B1") = "coucou"
 MsgBox "regarde"
 'et on restitue ici
 Range("A1:C10").Value = tablo

Il te suffit alors d'utiliser toute la plage (usedrange) au lieu d'une partie (et de supprimer les lignes qui pourraient avoir été rajoutées (celle qui dépassent les dimensions de tablo.
Mais tu ne retrouves ainsi que les valeurs, pas le reste.
Si tu veux tout (valeurs etc...) : copie la feuille entière sur une autre feuille et écrase après modifs la première feuille ^par la seconde
____________________
Vous aimez Codes-Sources ? Il vous aide ? Cliquez ici pour l'aider à continuer
Cliquer sur "Réponse acceptée"  en bas d'une solution adéquate est

Airone1CF03 · Answer

J'allais te répondre rapidement en te disant que ça ne marche pas. Et bien non, après l'action de supprimer, les valeurs sont bien restituées. Uniquement les valeurs, pas le format, mais c'est déjà bien.


Voilà donc ton exemple adapté à mon cas :


' Sauvegarde de la plage de données "A1:IV1000"
RangeSave = Range("A1:IV1000")
 
' Suppression de 10 lignes
 Rows("5:15").Delete

' Annulation (EN VALEUR donc) de la suppression et retour à l'état initial
 Range("A1:IV1000") = RangeSave



1 question et 1 problème, les 2 étant liés. De quel type doit être déclaré "RangeSave" ?

- En déclarant comme "Range", j'ai l'erreur suivante : "Variable objet ou variable de bloc With non définie" pour la ligne "RangeSave = Range("A1:IV1000")"

- En déclarant comme "Range()", j'ai l'erreur suivante : "Incompatibilité de type"


Faut-il le déclarer en "Variant", ou dans un autre type ?

Je pense que cette question est un peu le coeur de mon problème



Concernant mon problème, au lieu de sauvegarder la plage "A1:IV1000", je veux sauvegarder toute les cellules. En remplaçant donc "A1:IV1000" par Cells, la macro s'arrête, car la mémoire est insuffisante.

Il me semble que cela est lié au type de la variable. En quel type faut-il déclarer "RangeSave" pour pouvoir sauvegarder toutes les cellules ?



En conclusion, grâce à "ucfoutu", je sais réintroduire, sur une PLAGE LIMITEE (pas toutes les cellules), les valeurs (et pas les formats) supprimés.


Concernant la sauvegarde et la réintroduction des formats, j'ai vu une fois qu'on peut déclarer une variable selon un type personnalisé; ne serait-ce pas la solution.



En tout cas, merci pour vos réponses et merci de faire avancer le problème. 


Bonne soirée.

ucfoutu · Answer

Ce n'est pas exactement ce que je t'avais dit :
Essaye donc exactement ainsi :

Dim toto As String, rangesave
 rangesave = UsedRange.Value
 toto = UsedRange.Address
 MsgBox toto ' je le laisse pour toi... efface cette instruction ensuite
 
 Rows("5:15").Delete
 MsgBox "regarde ta feuille"
 Application.ScreenUpdating = False
 Range(toto).Value = rangesave
 Application.ScreenUpdating = True
 MsgBox "regarde ta feuille"


Et dis-moi ce que tu veux récupérer d('autre que les valeurs (on le fera le plus économiquement possible)...

____________________
Vous aimez Codes-Sources ? Il vous aide ? Cliquez ici pour l'aider à continuer
Cliquer sur "Réponse acceptée"  en bas d'une solution adéquate est

Airone1CF03 · Answer

Dim toto As String, rangesave
 rangesave = UsedRange.Value
 toto = UsedRange.Address
 MsgBox toto ' je le laisse pour toi... efface cette instruction ensuite
 
 Rows("5:15").Delete
 MsgBox "regarde ta feuille"
 Application.ScreenUpdating = False
 Range(toto).Value = rangesave
 Application.ScreenUpdating = True
 MsgBox "regarde ta feuille"



Le code bloque au niveau de "rangesave = UsedRange.Value" ("Erreur d'exécution '424': Objet requis"). Si on empêche l'exécution de cette ligne, il y a le même problème qui arrive à la ligne suivante. Encore une fois, je pense qu'il s'agit d'un problème de déclaration de variable.


Sinon j'avais bien saisi l'utilisation de UsedRange. Mais si tu te place dans le cas extrême où toutes tes cellules sont remplies, il y aura "Mémoire insuffisante", car la variable n'est pas déclarée de telle sorte qu'on puisse lui attribuer toutes les cellules de la feuille de données.


Enfin, dans l'idéal donc, j'aimerais que l'annulation de la suppression fasse revenir la feuille EXACTEMENT à son état d'origine. Il y aurait donc à récupérer les valeurs, les formats, les mises en forme. Qu'est ce qu'il peut y avoir d'autre ?



En attendant, merci une nouvelle fois pour ton aide.

userrrqi115 · Answer

Hello, 
L'instruction sendkeys permet de simuler une action manuelle sur le clavier.
Si l'on arrivait à coder des suppression de cellule, ligne ou colonne en utilisant cette instruction on aurait accès à undo.
Seulement je ne sais pas si cela est réalisable...
BR

USERRRQI115
Simple user
Great brain

ucfoutu · Answer

Bonjour, userrrqi115,

Tu plaisantes ou quoi ? 
Lorsque l'utilisateur supprime, il supprime (il ne passe pas par un sendkeys !) 

____________________
Vous aimez Codes-Sources ? Il vous aide ? Cliquez ici pour l'aider à continuer
Cliquer sur "Réponse acceptée"  en bas d'une solution adéquate est

userrrqi115 · Answer

Hello,

Lorsqu'un utilisateur supprime disons une cellule d'une feuille Excel, il produit une action manuelle via la souris la plupart de temps :clic droit puis supprimer.
En faisant cela tu as la possibilité d'utiliser le undo.
Si tu codes cette même action au sein d'une macro, plus possible d'annuler.

Un exemple simple sera peut-être plus parlant :

Sub efface_cell()
cells(1,1).clearcontents
End sub

Sub effacell_cell_sendkeys()
cells(1,1).select
Sendkeys "{DEL}",true
End sub

Si tu saisie une valeur en A1, en utilisant efface_cell tu effacera cette valeur sans pouvoir actionner le undo. L'autre macro elle t'en laissera la possibilité.
La limite de cette pseudo solution est comment transcrire une suppression de cellule via sendkeys 

BR

USERRRQI115
Simple user
Great brain

ucfoutu · Answer

Elle est bien bonne ...
Tu comptes donc empêcher (et comment ?) l'utilisateur d'utiliser la suppression normale, sans lancer ta macro ? 

____________________
Vous aimez Codes-Sources ? Il vous aide ? Cliquez ici pour l'aider à continuer
Cliquer sur "Réponse acceptée"  en bas d'une solution adéquate est

userrrqi115 · Answer

Hello, 
J'ai du mal interpréter le besoin : il me semblait que la suppression se faisait via une macro...
S'il s'agit d'une suppression manuelle, application.undo suffit pour revenir à l'étape précedente.
Certes ce retour arrière est limité au 16 dernières manip.
br

USERRRQI115
Simple user
Great brain

Airone1CF03 · Answer

[quote=userrrqi115]J'ai du mal interpréter le besoin : il me semblait que la suppression se faisait via une macro...
S'il s'agit d'une suppression manuelle, application.undo suffit pour revenir à l'étape précedente.
Certes ce retour arrière est limité au 16 dernières manip. /quote
En cas de suppression manuelle, on peut revenir en arrière, ce qui n'est pas le cas d'une suppression par macro.


[quote=ucfoutu]Dim toto As String, rangesave, lafeuille As Worksheet
 
 Set lafeuille = Sheets("Feuil1")
 With lafeuille
   rangesave = .UsedRange.Value
   RFormule = .UsedRange.Formula
   toto = .UsedRange.Address
   MsgBox toto ' je le laisse pour toi... efface cette instruction ensuite
 
   .Rows("5:15").Delete
   MsgBox "regarde ta feuille"
   Application.ScreenUpdating = False
   .Range(toto).Value = rangesave
   .Range(toto).Formula = RFormule
   Application.ScreenUpdating = True
   MsgBox "regarde ta feuille"
 End With/quote
Déjà j'utilise Excel 2003. Ensuite, le code marche bien. Il fallait déclarer la feuille active pour que ça marche.


Ensuite, je me heurte à la limite du UsedRange : c'est à dire que si la sélection est trop importante en cellules avec en plus divers formats, la mémoire est insuffisante. Si quelqu'un sait comment résoudre ce problème, merci de le dire. Mais dans la pratique, ça devrait largement suffire.


Enfin, j'avais dis qu'Excel faisait facilement l'annulation d'une action. Après réflexion, je me suis dit qu'Excel était capable d'annuler n'importe quelle action. Je pense donc que la feuille active doit être sauvegardée temporairement à chaque action effectuée, et lorsque l'utilisateur décide d'annuler une action réalisée, Excel restitue la version de la feuille avant cette action.


Donc voilà, merci ucfoutu pour cette solution. Est-ce que tu serais capable de restituer en plus des valeurs et des formules le format des cellules supprimées ?



Encore une fois, merci pour votre aide et bonne journée.

ucfoutu · Answer

Non  ... oi y parviendrait, mais cela coûterait plus cher en ressources (j'ai essayé) que la copie complète sur une feuille tremplin puis l'écrasement de la feuille originelle par la feuille tremplin !
Seules les restitution des seules valeurs et formules sont "meilleur marché".

____________________
Vous aimez Codes-Sources ? Il vous aide ? Cliquez ici pour l'aider à continuer
Cliquer sur "Réponse acceptée"  en bas d'une solution adéquate est

Airone1CF03 · Answer

Comme a pu le dire userrrqi115, ce que je cherche à faire idéalement est trop coûteux en mémoire.


J'ai donc pris la méthode de ucfoutu (sauvegarde des valeurs et formules supprimées, puis restitution de celles-ci), en demandant par "MsgBox" la confirmation à l'utilisateur de sa volonté de supprimer les lignes ou colonnes. 

J'ai aussi géré le fait que la sauvegarde des données prenne trop de place (toutes les cellules de la feuille de calcul pleines) en renvoyant un message d'erreur disant que la restitution des données supprimées est impossible.


Merci à tous pour votre aide.

userrrqi115 · Answer

Hello, 
Tout cela fut très intéressant. Ci-dessous le code de MR Ucefoutu avec la prise en compte du format, une question me taraude :
existe-t-il un moyen d'évaluer les ressources nécessaires à l'exécution des macro?

Sub Macro1()
'
' Macro1 Macro
'
Dim toto As String, rangesave, lafeuille As Worksheet
 
 Set lafeuille = Sheets("Feuil1")
 With lafeuille
   rangesave = .UsedRange.Value
   RFormule = .UsedRange.Formula
   RnberFormat = .UsedRange.NumberFormat
   
   toto = .UsedRange.Address
   MsgBox toto ' je le laisse pour toi... efface cette instruction ensuite
 
   .Rows("5:15").Delete
   MsgBox "regarde ta feuille"
   Application.ScreenUpdating = False
   .Range(toto).Value = rangesave
   .Range(toto).Formula = RFormule
   .Range(toto).NumberFormat = RnberFormat
   Application.ScreenUpdating = True
   MsgBox "regarde ta feuille"
 End With
'
End Sub

BR

USERRRQI115
Simple user
Great brain

[VBA] Macro ANNULER une suppression (par macro) de cellules dans une feuille

17 réponses

Votre réponse

Discussions similaires