Microsoft 365 Trouver la dernière colonne utilisé sur une ligne

[LORDDD]

Bonjour à vous tous,

J'ai une formule qui fonctionne bien pour la création de tableau pour des recherche sur des lignes :

For Each c In Sheets("Para").Range("i2:i" & [i65000].End(xlUp).Row)
    If Dico1.Exists(c.Value2) Then col = Dico1(c.Value2) Else Dico1(c.Value2) = Mcol: col = Mcol: Mcol = Mcol + 1
Next c

Par contre impossible de l'adapter au niveau des colonnes :

For Each c In Sheets("Tab1").Range("c3:" & [c3].End(xlToRight).colunms)
    If Dico1.Exists(c.Value2) Then col = Dico1(c.Value2) Else Dico1(c.Value2) = Mcol: col = Mcol: Mcol = Mcol + 1
Next c

Si quelqu'un peu m'aider, je suis preneur, par avance merci a vous tous

 

[M12]

Bonjour
place plutôt xlToLeft au lieu de xlToRight

 

[LORDDD]

Bonjour
place plutôt xlToLeft au lieu de xlToRight

Merci de ton retour, mais dans les deux cas message d'erreur et cela ne fait pas ce que je souhaite, il ne met pas dans mon range le mon sous forme "aa3" de ma dernière colonne.

 

[LORDDD]

Je crois que je viens de trouver :

For Each c In Sheets("Tab1").Range("c3:" & [c3].End(xlToRight).address)
    If Dico1.Exists(c.Value2) Then col = Dico1(c.Value2) Else Dico1(c.Value2) = Mcol: col = Mcol: Mcol = Mcol + 1
Next c

Mettre adresse, je continue mon test

 

[Dudu2]

Bonjour,
Ce que j'utilise et qui n'est pas sensible aux filtres résultat d'une discussion avec des Barbapapatruc du coin .

'-------------------------------------------------------------
'Calcul de la dernière colonne non vide d'une ligne par Match
'DernièreColonne = DernièreColonneEnLigne(ActiveSheet.Rows(1))
'-------------------------------------------------------------
Function DernièreColonneEnLigne(ByVal Ligne As Range) As Long
    Const ChaineMax as String = "zzzzzzzzzzzzzzzzzzzz"
    Const NombreMax as Double = (2 ^ 53 - 1) * 2 ^ 971

    If Not Ligne Is Nothing Then
        With Application
            DernièreColonneEnLigne = .Max(.IfError(.Match(ChaineMax, Ligne.Rows(1)), 0), _
                                          .IfError(.Match(NombreMax, Ligne.Rows(1)), 0))
        End With
    End If
End Function

Et le symétrique pour les lignes.

'----------------------------------------------------------------------
'Calcul de la dernière ligne non vide d'une colonne avec fonction Match
'DernièreLigne = DernièreLigneEnColonne(ActiveSheet.Columns(1))
'----------------------------------------------------------------------
Function DernièreLigneEnColonne(ByVal Colonne As Range) As Long
    Const ChaineMax as String = "zzzzzzzzzzzzzzzzzzzz"
    Const NombreMax as Double = (2 ^ 53 - 1) * 2 ^ 971

    If Not Colonne Is Nothing Then
        With Application
            DernièreLigneEnColonne = .Max(.IfError(.Match(ChaineMax, Colonne.Columns(1), 1), 0), _
                                          .IfError(.Match(NombreMax, Colonne.Columns(1), 1), 0))
        End With
    End If
End Function

 

[LORDDD]

Bonjour,
Ce que j'utilise et qui n'est pas sensible aux filtres résultat d'une discussion avec des Barbapapatruc du coin .

'-------------------------------------------------------------
'Calcul de la dernière colonne non vide d'une ligne par Match
'DernièreColonne = DernièreColonneEnLigne(ActiveSheet.Rows(1))
'-------------------------------------------------------------
Function DernièreColonneEnLigne(ByVal Ligne As Range) As Long
    Const ChaineMax as String = "zzzzzzzzzzzzzzzzzzzz"
    Const NombreMax as Double = (2 ^ 53 - 1) * 2 ^ 971

    If Not Ligne Is Nothing Then
        With Application
            DernièreColonneEnLigne = .Max(.IfError(.Match(ChaineMax, Ligne.Rows(1)), 0), _
                                          .IfError(.Match(NombreMax, Ligne.Rows(1)), 0))
        End With
    End If
End Function

Et le symétrique pour les lignes.

'----------------------------------------------------------------------
'Calcul de la dernière ligne non vide d'une colonne avec fonction Match
'DernièreLigne = DernièreLigneEnColonne(ActiveSheet.Columns(1))
'----------------------------------------------------------------------
Function DernièreLigneEnColonne(ByVal Colonne As Range) As Long
    Const ChaineMax as String = "zzzzzzzzzzzzzzzzzzzz"
    Const NombreMax as Double = (2 ^ 53 - 1) * 2 ^ 971

    If Not Colonne Is Nothing Then
        With Application
            DernièreLigneEnColonne = .Max(.IfError(.Match(ChaineMax, Colonne.Columns(1), 1), 0), _
                                          .IfError(.Match(NombreMax, Colonne.Columns(1), 1), 0))
        End With
    End If
End Function

Merci de ta contribution je vais tester ca

 

[mapomme]

Bonjour @LORDDD , @Dudu2 ,

Une version différente (c'est selon le choix de l'utilisateur)

Pour ne pas être "embêté" par le fameux caractère "chaine nulle" (que je considère comme une cellule vide), on peut utiliser la fonction DerColNonVide(x As Range.). x est une cellule ou une plage au sein de la ligne à considérer.

Ce caractère "chaine nulle" survient surtout quand on colle en valeur des résultats de formules dont le résultat est la chaine vide "" .

Function DerColNonVide(x As Range) As Long
Dim xrg, t, j&
   Set xrg = Intersect(x.EntireRow, x.Parent.UsedRange)
   If xrg Is Nothing Then Exit Function
   t = xrg.Formula
   For j = UBound(t, 2) To 1 Step -1
      If Len(t(1, j)) > 0 Then DerColNonVide = j: Exit Function
   Next j
End Function

nota: il est préférable de prendre en argument la ligne entière (ex: 3:3) car alors la fonction se met automatiquement à jour si on ajoute ou supprime une valeur au sein de la ligne 3.

 

[Dudu2]

Bonjour @mapomme,

Pour ne pas être "embêté" par le fameux caractère "chaine nulle" (que je considère comme une cellule vide)

C'est en effet un point à souligner sur cette différence d'interprétation où tu considères une cellule contenant chaine vide qui n'est pas le résultat d'une formule comme une cellule vide.

De mon point de vue une chaine vide est une valeur de chaine comme 0 est une valeur de nombre.
Et puis il y a cette différenciation entre valeur native et résultat de formule qui me gène aussi.
Mais bon, admettons !

Faut dire que pour obtenir une chaine vide qui n'est pas issue d'une formule, cela ne peut se faire qu'en VBA en affectant explicitement une chaine vide (Cellule.Value = "") dans une cellule dont le format est Texte.
Si le format est Standard, le résultat est une cellule vide. Et donc le résultat de "ta" fonction est plus cohérent car identique quelque soit le format de cellule quand celui de "ma" fonction varie selon ce format.

Hors formule, on ne peut pas obtenir une chaine vide dans une cellule de format Texte par l'interface graphique (l'équivalent de Cellule.Value = ""). Un <Suppr> dans cette cellule résulte en une cellule vide et non une cellule contenant une valeur de chaine vide (probablement l'équivalent d'un Cellule.ClearContents).

 

[Rouge]

Bonjour,

Je n'ai pas eu le courage de lire toutes les réponses, mais d'après ce que je vois dans la demande initiale, il manque un élément dans la ligne de code suivante:

   For Each c In Sheets("Tab1").Range("c3:" & [c3].End(xlToRight).colunms)

devrait s'écrire plutôt:

   For Each c In Sheets("Tab1").Range("C3:C" & [c3].End(xlToRight).colunms)

Cdlt

 

[ChTi160]

Bonjour LORDDD
Bonjour le Fil ,le Forum
il faut aussi écrire

.Column et Non .Colunm

jean marie

 

[mapomme]

Faut dire que pour obtenir une chaine vide qui n'est pas issue d'une formule,

Oui mais c'est relativement fréquent de l'obtenir. Il suffit de copier une plage avec des formules retournant la chaine vide (et ça il y en a à la pelle) et de la recopier en valeur vers ailleurs ou bien tout simplement de copier la plage vers elle-même en valeur.
Comme je dis: c'est à l'utilisateur de savoir ce qu'il recherche.
Ce qui m'a fait faire cette fonction, c'est que je recherchais la dernière cellule "pseudo vide" pour la peupler.

Je sais que la chaine vide n'est pas vide mais souvent j'ai besoin de la considérer comme tel.

 

[Dudu2]

Il suffit de copier une plage avec des formules retournant la chaine vide et de la recopier en valeur

En effet, tu as raison, je n'ai pas pensé à cette manipulation.

 

[Dudu2]

Fort de ces enseignements, j'ai ajouté des options dans la fonction pour ignorer les chaines vides.

'----------------------------------------------------------------------
'Calcul de la dernière ligne non vide d'une colonne avec fonction Match
'DernièreLigne = DernièreLigneEnColonne(ActiveSheet.Columns(1))
'IgnoreNullStringConstant = Ignore les valeurs de chaine vide
'                           en tant que valeurs constantes
'IgnoreNullStringFormula = Ignore les valeurs de chaine vide
'                          en tant que valeurs de formules
'----------------------------------------------------------------------
Function DernièreLigneEnColonne(ByVal Colonne As Range, _
                                Optional IgnoreNullStringConstant As Boolean = False, _
                                Optional IgnoreNullStringFormula As Boolean = False) As Long
    Const ChaineMax As String = "zzzzzzzzzzzzzzzzzzzz"
    Const NombreMax As Double = (2 ^ 53 - 1) * 2 ^ 971
    Dim Cel As Range
 
    If Not Colonne Is Nothing Then
        With Application
            DernièreLigneEnColonne = .Max(.IfError(.Match(ChaineMax, Colonne.Columns(1), 1), 0), _
                                          .IfError(.Match(NombreMax, Colonne.Columns(1), 1), 0))
        End With
    
        'Ignorer les cellules dont la valeur constante ou formule est une chaine vide
        If IgnoreNullStringConstant Or IgnoreNullStringFormula Then
            Do While DernièreLigneEnColonne > 0
                Set Cel = Colonne.Parent.Cells(DernièreLigneEnColonne, Colonne.Column)
            
                'Ne pas grouper les tests (cas VarType(Cel.Value) = vbError)
                If VarType(Cel.Value) <> vbString Then Exit Do
                If Len(Cel.Value) > 0 Then Exit Do
                If Not (IgnoreNullStringFormula And Len(Cel.Formula) > 0) _
                And Not (IgnoreNullStringConstant And Len(Cel.Formula) = 0) Then Exit Do
            
                'Cellule contenant une chaine vide
                DernièreLigneEnColonne = DernièreLigneEnColonne - 1
            Loop
        End If
    End If
End Function

'-------------------------------------------------------------
'Calcul de la dernière colonne non vide d'une ligne par Match
'DernièreColonne = DernièreColonneEnLigne(ActiveSheet.Rows(1))
'IgnoreNullStringConstant = Ignore les valeurs de chaine vide
'                           en tant que valeurs constantes
'IgnoreNullStringFormula = Ignore les valeurs de chaine vide
'                          en tant que valeurs de formules
'-------------------------------------------------------------
Function DernièreColonneEnLigne(ByVal Ligne As Range, _
                                Optional IgnoreNullStringConstant As Boolean = False, _
                                Optional IgnoreNullStringFormula As Boolean = False) As Long
    Const ChaineMax As String = "zzzzzzzzzzzzzzzzzzzz"
    Const NombreMax As Double = (2 ^ 53 - 1) * 2 ^ 971
    Dim Cel As Range

    If Not Ligne Is Nothing Then
        With Application
            DernièreColonneEnLigne = .Max(.IfError(.Match(ChaineMax, Ligne.Rows(1), 1), 0), _
                                          .IfError(.Match(NombreMax, Ligne.Rows(1), 1), 0))
        End With
    
        'Ignorer les cellules dont la valeur constante ou formule est une chaine vide
        If IgnoreNullStringConstant Or IgnoreNullStringFormula Then        
            Do While DernièreColonneEnLigne > 0
                Set Cel = Ligne.Parent.Cells(Ligne.Row, DernièreColonneEnLigne)
            
                'Ne pas grouper les tests (cas VarType(Cel.Value) = vbError)
                If VarType(Cel.Value) <> vbString Then Exit Do
                If Len(Cel.Value) > 0 Then Exit Do
                If Not (IgnoreNullStringFormula And Len(Cel.Formula) > 0) _
                And Not (IgnoreNullStringConstant And Len(Cel.Formula) = 0) Then Exit Do
            
                'Cellule contenant une chaine vide
                DernièreColonneEnLigne = DernièreColonneEnLigne - 1
            Loop
        End If
    End If
End Function