XL 2013 suppression de lignes = formules et plages modifiées

[Usine à gaz]

Bonjour à toutes et à tous,

Me re-voilà devant un problème que j'ai depuis très longtemps.
Jusqu'à maintenant, je m'étais "débrouillé" en modifiant par ci et par là LOL , mais aujourd'hui, mes fichiers importants m'obligent à chercher une vraie solution.

Voici le problème ( dans le fichier joint) :
Quand je supprime des lignes entre la ligne 3 et 100, ma formule et ma plage sont modifiées

Mes recherches
J'ai fait des recherches et j'ai vu qu'il était possible de "bloquer", par exemple F3 à F100
avec ".End(xlUp)" ou que la plage de calculs ou de base soit toujours la dernière ligne NON vide

Mais je n'ai pas trouvé comment "adapter" pour que ça marche.

Encore une fois, je fais appel à vous pour m'aider (pj fichier test)
Un grand merci pour m'avoir lu.
Amicalement,
Lionel,

 

[Yurperqod]

Bonjour à tous

Est-ce que cette formule en F2 en remplacement de la tienne peut te convenir ?
=SOUS.TOTAL(9;F:F)/2

Et pour la plage nommée base, tu peux utiliser une macro qui recrée le nom

Sub MacroTest()
ActiveWorkbook.Names.Add Name:="base", RefersToR1C1:="=Feuil1!R3C6:R102C6"
End Sub

Il faut penser à remplacer Feuil1 par le vrai nom de la feuille.

 

[Usine à gaz]

Bonjour Ce lien n'existe plus,

Merci de m'avoir répondu et aussi vite.
Malheureusement, la formule semble ne pas fonctionner car excel m'indique une référence circulaire.
Amicalement,
Lionel,

 

[Yurperqod]

Et avec cette formule ?
=SOUS.TOTAL(9;INDIRECT("F3:F102"))

Et la macro, tu as testé ?

 

[Usine à gaz]

Re-Bonjour,

ça marche super. je vais continuer de tester mais c'est bon.

Sauriez-vous faire la même chose pour définir (nommer) une plage avec votre formule ?
INDIRECT("F3:F1048576"))

Mais déjà un grand merci,
Amicalement,
Lionel,

 

[Yurperqod]

Voici la macro modifiée

Sub MacroTest2()
Dim Lignes As String
Lignes = Application.Rows.Count
ActiveWorkbook.Names.Add Name:="base", RefersToR1C1:="=Feuil1!R3C6:R" & Lignes & "C6"
End Sub

 

[Usine à gaz]

Je n'avais pas vu la macro. Mon fichier de travail est gros et avec un monbre de macros très importants.
LOL, moins j'en ajoute et mieux je me porte.

Est-il possible de le faire directement dans le gestionnaire de nom avec une formule ?

 

[Yurperqod]

Avec une formule, en cas de suppression de lignes , la référence des cellules change dans la plage nommée.
Voila une formule pour nommée une plage de manière dynamique
=DECALER(Feuil1!$F$3;0;0;NBVAL(Feuil1!$F$3:$F$102);1)

mais comme je disais, si on supprime des lignes, la référence change.

C'est pour ca que je suis passé par une macro pour nommer la plage.

 

[Usine à gaz]

Merci pour ta gentillesse et pour ta volonté de m'aider.
Tu m'as déjà donné une formule qui fonctionne et c'est déjà beaucoup.

Concernant la définition de la plage, j'en ai tellement dans mon fichier que je préfère éviter les macros.
Je vais continuer à chercher une solution pour cela.
Amicalement,
Lionel,

 

[Usine à gaz]

LOL, je n'avais pas vur ton message précédent ..... je regarde

 

[Usine à gaz]

Super, cette formule dans la sélection d'une plage fonctionne à merveille.
(=DECALER(Feuil1!$F$3;0;0;NBVAL(Feuil1!$F$3:$F$102);1))

Voilà un grand service que tu me rends.
Je t'en remercie vraiment
Amicalement,
Lionel,

 

[Usine à gaz]

Plage nommée
J'ai re-testé la formule : =DECALER(Feuil1!$F$3;0;0;NBVAL(Feuil1!$F$3:$F$1048576);1)
En supprimant des tas de lignes = pas de changement de formule
En ajoutant des tas de lignes = pas de changement de formule

 

[Usine à gaz]

Oui, je verrai à l'usage.

D'autre part, il y a ce code ".End(xlUp)" qui semble aussi sollutionner le souci de suppression ou d'ajout de lignes mais je n'arrive pas à le mettre dans les formules.

 

[Yurperqod]

Si vraiment tu veux utiliser End(xlUp) pour ta formule, il faut passer par une macro

Sub MacroTest3()
Dim Ligne As Long
Ligne = Cells(Rows.Count, "F").End(xlUp).Row
ActiveWorkbook.Names.Add Name:="base", RefersToR1C1:="=OFFSET(Feuil1!R3C6,0,0,COUNTA(Feuil1!R3C6:R" & Ligne & "C6),1)"
End Sub

 

[Usine à gaz]

OK merci beaucoup pour cette information.