Modifier une plage nommée en VBA

Bonjour à tous,

J'ai une petite problématique.

J'ai créé une procédure pour definir automatiquement un plage nommée au lancement d'Excel:

' definir une nouvelle plage
Sub DefPlage()
Dim r As Range
Dim lifin As Long

' Definition de la plage BU
lifin = Sheets("Source").Cells(1, 1).End(xlToRight).Column
Set r = Sheets("Source").Range(Cells(1, 1), Cells(1, lifin))
ActiveWorkbook.Names.Add Name:="BU", RefersTo:="=Source!" & r.Address
End Sub

Elle fonctionne nickel et j'ai une plage contenant par exemple "TRUC1;TRUC2;TRUC3"

Maintenant je veux definir une procédure pour mettre à jour cette Plage nommée en cas de modif.

J'utilise ce code:

Sub ModPlage()
Dim plage  As String
Dim lifin As Long

' Modification de la plage BU
lifin = Sheets("Source").Cells(1, 1).End(xlToRight).Column
plage = Sheets("Source").Range(Cells(1, 1), Cells(1, lifin)).Address
ActiveWorkbook.Names("BU").RefersToR1C1 = plage
End Sub

Et là ça ne fonctionne plus car quand je modifie ma plage depuis Excel, la plage nommée dont la valeur est "="$A$1:$D$1".... En fait il y a les " qui viennent mettre le bordel.

Une idée pour résoudre cela?

Merci

bonjour,

essaie ceci

ActiveWorkbook.Names("BU").RefersTo = plage

Hello

J'avais déjà testé cela et c'est le même résultat....

C'est bon, j'ai trouvé.... fallait mettre:

ActiveWorkbook.Names("BU").RefersTo = "=Source!" & plage

Salut Kev

Une autre manière de faire, c'est on jamais :

Sub ModPlage()
ActiveWorkbook.Names("BU").RefersTo = "=$A$1:$" & Split(Columns(Range("XFD1").End(xlToLeft).Column).Address(ColumnAbsolute:=False), ":")(1) & "$1"
End Sub

Tout sa loge en une ligne!

Salut

Merci pour cette autre solution.

Re

Il faudrait un exemple / un cas avec ta demande que je puisse te l'illustrer, mais voilà :

Sub RowCountSimplifié()
Dim MaColonneEnLettre As String
Dim MaColonneEnVar As Long
Dim NbrLigneDansMaColonne As Long
MaColonneEnVar = Range("XFD1").End(xlToLeft).Column
MaColonneEnLettre = Split(Columns(MaColonneEnVar).Address(ColumnAbsolute:=False), ":")(1)
NbrLigneDansMaColonne = Range(MaColonneEnLettre & Rows.Count).End(xlUp).Row
MsgBox NbrLigneDansMaColonne
End Sub

En version une ligne :

Sub RowCount()
MsgBox Range(Split(Columns(Range("XFD1").End(xlToLeft).Column).Address(ColumnAbsolute:=False), ":")(1) & Rows.Count).End(xlUp).Row
End Sub

Avec ces deux codes, tu compte le nombre de ligne de la colonne la plus à droite de ton fichier Excel

Après faut adapter au besoin!

bonjour,

voici 2 manières de faire (entre-autres)

dl = Cells(Rows.Count, 1).End(xlUp).Row 'dernière ligne
pl = 2 ' première ligne
nl = Range(Cells(2, 1), Cells(dl, 1)).Rows.CountLarge 'nombre de lignes d'une plage
'ou
nl = dl - pl + 1 'nombre de lignes

salut

j'ai reussi a faire ce que je voulais avec ce code.... probablement pas très propre mais bon ça fonctionne;

Sub DefPlage()
Dim r As Range
Dim i, lifin, colfin As Long
Dim MyName As String
Dim StartCell As Range

' Definition du Range BU
With Sheets("Source")
colfin = .Cells(1, 1).End(xlToRight).Column
Set r = .Range(Cells(1, 1), Cells(1, colfin))
ActiveWorkbook.Names.Add Name:="BU", RefersTo:="=Source!" & r.Address

' Definition des differentes Ranges de BU
On Error Resume Next
For i = 1 To colfin
MyName = Replace(Cells(1, i).Value, " ", "_")
Set StartCell = Range(Cells(2, i), Cells(2, i))
lifin = .Cells(.Rows.Count, StartCell.Column).End(xlUp).Row
Set r = .Range(Cells(2, i), Cells(lifin, i))
ActiveWorkbook.Names.Add Name:=MyName, RefersTo:="=Source!" & r.Address
Next
End With

End Sub