Rendre une plage dynamique

[Sebast]

Je cherche le moyen de déterminer une plage de façon dynamique, c'est-à-dire à définir la zone partant de la cellule B2 jusqu’au bas de la colonne non pas B2 mais Gi (ex : de A2 à G25754 ou G857 ou Gi, en fonction de la présence de données en colonne G)
Je connais la fonction ActiveSheet.UsedRange.Rows.Count permettant d’aller en bas de la colonne mais elle ne couvre que la colonne en question (ici colonne A)

En effet, dans ma colonne A, il arrive que des cellules soient vides mais que des données existent en colonne G, si bien que ma plage serait restreinte à tort en utilisant ActiveSheet.UsedRange.Rows.Count

Je pensais pouvoir affecter le numéro de ligne avec un indice mais ça plante (je ne sais pas si c’est lié à la concaténation ou à la façon d’affecter la plage au nom

Voici mon code :

Sub Définir_Plage()
Sheets("support").Range("G2").Activate
Dim numlign As Long
numlign = ActiveSheet.UsedRange.Rows.Count
Range("A1:G&numlign").Name = "Selection_Zone"
‘ opérations sur la plage ainsi définie
End Sub

Merci à tous pour vos lumières !

 

[tototiti2008]

Re : Rendre une plage dynamique

Bonjour Sebast,

Le plantage est lié à la concaténation

essaye avec la ligne comme ça :

Range("A1:G" & numlign).Name = "Selection_Zone"

En effet, dans ma colonne A, il arrive que des cellules soient vides mais que des données existent en colonne G, si bien que ma plage serait restreinte à tort en utilisant ActiveSheet.UsedRange.Rows.Count

Non, UsedRange s'applique à toute la feuille et donc tiendra compte de toutes les colonnes. Cependant UsedRange n'est à utiliser que si ta ligne 1 contient des données, car UsedRange ne commence pas forcément à la 1ère ligne

Edit : Bonjour Philippe 😉

 

[phlaurent55]

Re : Rendre une plage dynamique

Bonjour Sébast

Range("B2:G"&Range("G65535").End(XlUp).Row)

à+
Philippe

Edit: Bonjour Tototiti

 

[JNP]

Re : Rendre une plage dynamique

Bonsoir le fil 🙂,
Comme dit TotoTiti, il y a un risque si les premières lignes sont vides, mais Philippe, par contre, ne tient compte que de G 🙄...
Donc, quitte à faire un peu lourd 😛...

Sub Définir_Plage()
Dim numlign As Long
numlign = Application.WorksheetFunction.Max(Range("A65535").End(xlUp).Row, _
    Range("B65535").End(xlUp).Row, Range("C65535").End(xlUp).Row, _
    Range("D65535").End(xlUp).Row, Range("E65535").End(xlUp).Row, _
    Range("F65535").End(xlUp).Row, Range("G65535").End(xlUp).Row)
Range("A1:G" & numlign).Name = "Selection_Zone"
End Sub

Bonne soirée 😎

 

[tototiti2008]

Re : Rendre une plage dynamique

Bonjour JNP,
Re,

ou alors 😉

Sub Définir_Plage()
Dim numlign As Long
numlign = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.range("A1").Row -1
Range("A1:G" &numlign).Name = "Selection_Zone"
End Sub

 

[Sebast]

Re : Rendre une plage dynamique

Bonsoir,

un grand merci, je n'aurais jamais trouvé seul alors qu'au final, c'est assez simple !

Sebast

 

[Sebast]

Re : Rendre une plage dynamique

Bonsoir,

merci beaucoup pour ton aide très complète !

Sebast

 

[Hippolite]

Re : Rendre une plage dynamique

Bonsoir à tous,

Je signale une compilation intéressante : :[VBA-E] Dernière ligne et dernière colonne renseignées d'une feuille de calculs - Forum des professionnels en informatique

D'autre part UsedRange n'est pas toujours fiable, il peut rester des lignes vides, je ne me souviens plus dans quelle configuration cela m'était arrivé.

Utiliser des formules de type Range("B65535").End(xlUp) peut poser des problèmes de compatibilité entre les versions d'Excel qui n'ont pas le même nombre de lignes dans une feuille. Cela peut être contourné en comptant les lignes de la feuille et en affectant le résultat à une variable.

A+

 

[ROGER2327]

Re : Rendre une plage dynamique

Bonjour à tous

Je verrais bien quelque chose de ce genre :

Sub defZone()
  tata Range("B2"), Range("G:G"), "Zone_sélectionnée" 'modifiable à volonté
End Sub

Private Sub tata(r1 As Range, r2 As Range, nomZone$)
Dim i&, l&, c&, tmp&, plg As Range
  c = r2.Column
  With r1
    With Range("G:G"): c = IIf(c < .Column, c, .Column): End With
    For i = c To .Column Step -1
      tmp = Cells(Rows.Count, i).End(xlUp).Row
      If l < tmp Then l = tmp
    Next
    If l >= .Row And c >= .Column Then Set plg = .Resize(1 + l - .Row, IIf(1 + c - .Column, 1 + c - .Column, 1))
  End With
  If Not plg Is Nothing Then
    plg.Name = nomZone
    plg.Select
  Else 'facultatif
     On Error Resume Next
    Names(nomZone).Delete
    On Error GoTo 0
    MsgBox "Que dalle !"
  End If
End Sub

(Après en avoir un temps usé et abusé, je me méfie des CurrentRegion, UsedRange et autre SpecialCells(xlCellTypeLastCell) dont le comportement n'est pas toujours des plus compréhensibles par ma petite cervelle.)
​

ROGER2327
#5020

Mardi 10 Pédale 138 (Nativité de Saint Tancrède, jeune homme, SQ)
14 Ventôse An CCXIX
2011-W09-5T01:24:19Z