XL 2016 Selectionner une ligne jusqu'à dernière colonne

[maroon]

Bonjour!

Avec la macro que je fais j'aimerais mettre en couleur certaines lignes: des lignes vides qui séparent certaines parties du tableau. Je connais le numéro des lignes mais comme le nombre de colonnes varient je détermine le numéro de la dernière colonne avec le code:

ActiveCell.SpecialCells(xlLastCell).Column

Donc si je souhaite sélectionner la ligne vide numéro 15 par exemple jusqu'à la dernière colonne du tableau comment je dois écrire le code? Pour ensuite lui affecter une mise en forme.

Je n'arrive pas à trouver la syntaxe en utilisant le morceau de code que j'ai mis juste au-dessus.

Merci d'avance si quelqu'un peut m'aider!

 

[maroon]

Re

Et la version Oneliner

Sub Colorier_Lignes_Vides_II()
[Indicateurs].Resize(, ActiveSheet.UsedRange.Columns.Count).Offset(1).SpecialCells(4).Interior.Color = RGB(255, 78, 127)
End Sub

Je ne vais pas chercher à tout comprendre maintenant mais que signifie le "4" dans la fonction Specialcells? De plus j'ai remarqué que ce n'est pas uniquement les lignes mais aussi les cellules vides qui sont prises en compte... donc quand le tableau n'est pas totalement remplie ça pose problème...

 

[Staple1600]

Re

4: c'est la valeur de xlCellTypeBlanks
On peut écrire soit l'un soit l'autre.

 

[maroon]

Re

4: c'est la valeur de xlCellTypeBlanks
On peut écrire soit l'un soit l'autre.

Ok merci, je comprends mieux maintenant!

 

[mapomme]

Bonjour à tous,

Dans mon post précédent, j'ai commis des fonction verbeuses (qui fonctionnaient).
Mais il a des cas qui les prenaient en défaut (enfin ce que j'ai trouvé pour l'instant):

• quand dans une cellule de la ligne se trouvait une valeur d'erreur
• quand dans une cellule se trouvait la valeur Faux ou Vrai

J'ai donc (re)fait une fonction plus simple qui devait prendre en compte aussi ces cas en plus des autres cités dans mon précédent message (une ligne entière ou une plage au sein d'une ligne, colonnes masquées, tableaux structurés, cellules contenant la chaine "" assimilées à des cellules vides, ..., etc.).

Il n'y a qu'une fonction qui retourne le dernier numéro de colonne ayant une valeur:

• Quand aucune cellule ne comporte de valeur, on retourne 0
• On peut soit retourner le n° absolu de la colonne (par rapport à la colonne N° 1 de la feuille), soit le numéro relatif (par rapport à la colonne N° 1 de la plage examinée
• Si la plage examinée comporte plusieurs lignes ou plusieurs plages disjointes, on retourne une erreur (#REF!)

La fonction : DerCol(xrg As Range, Optional relatif)

• où xrg est la plage à examiner
• où relatif indique ce qu'on doit retourner. Si relatif est absent, alors on retourne le N° absolu de colonne. Si relatif est présent, alors on retourne la N° relatif de colonne.

Exemple d'utilisation sur une feuille:
=DerCol ( 18:18 ) -> on retourne le n° absolu de la dernière colonne avec valeur de la ligne 18
=DeCcol ( 18:18 ; 0 ) -> on retourne le n° relatif de la dernière colonne avec valeur de la ligne 18
=DerCol ( C8:K8 ) -> on retourne le n° absolu de la dernière colonne avec valeur de la plage C8:K8
=DerCol ( C8:K8 ; 1 ) -> on retourne le n° relatif de la dernière colonne avec valeur de la plage C8:K8

Exemple d'utilisation en VBA:

Sub Test1()
Dim der
   der = DerCol(Range("C7:L7"), 1)     ' en relatif
   If Not IsError(der) And der <> 0 Then Range("C7:L7").Resize(, der).Select
End Sub

Sub Test2()
Dim der
   der = DerCol(Rows(44))     ' en absolu
   If Not IsError(der) And der <> 0 Then Rows(44).Resize(, der).Select
End Sub

le fichier joint utilise la fonction sur une feuille de calcul. Il y a aussi deux boutons "Test" pour l'usage en VBA. le code de la fonction est dans module1.

Si d'aucun veulent tester et m'indiquer ce qu'il en est... Merci

 

[mapomme]

Re,

Et pour ceux qui craignent les virus (à juste raison), le code de la fonction :

Function DerCol(xrg As Range, Optional relatif)
Const Formul = "=LARGE(IF(IF(ISERROR(Plage),COLUMN(Plage),-1)>0,IF(ISERROR(Plage),COLUMN(Plage),-1),IF(Plage<>"""",COLUMN(Plage),"""")),1)"
Dim yrg As Range, x, f

   If xrg Is Nothing Then DerCol = CVErr(xlErrRef): Exit Function
   If xrg.Rows.Count > 1 Then DerCol = CVErr(xlErrRef): Exit Function
   If xrg.Areas.Count > 1 Then DerCol = CVErr(xlErrRef): Exit Function
   With xrg.Parent
      If xrg.Column + xrg.Columns.Count > .UsedRange.Column + .UsedRange.Columns.Count Then
         Set yrg = Intersect(xrg, .Rows(xrg.Row).Resize(, .UsedRange.Column + .UsedRange.Columns.Count - 1))
      End If
   End With
   f = Replace(Formul, "Plage", xrg.Address(0, 0))
   x = Application.Evaluate(f)
   If IsError(x) Then x = 0
   If x <> 0 Then DerCol = x + IIf(IsMissing(relatif), 0, 1 - xrg.Column) Else DerCol = 0
End Function

 

[Staple1600]

RE

Merci mapomme
Mais on ne peut pas diffuser ta fonction au Portugal.
Je tenais à t'en informer

EDITION: Sinon tu peux , stp, expliquer:
Optional relatif
et ceci
If x <> 0 Then DerCol = x + IIf(IsMissing(relatif), 0, 1 - xrg.Column) Else DerCol = 0

 

[mapomme]

Sinon tu peux , stp, expliquer:
Optional relatif
et ceci
If x <> 0 Then DerCol = x + IIf(IsMissing(relatif), 0, 1 - xrg.Column) Else DerCol = 0

Optional indique un argument de fonction ou de subroutine qui peut être présent ou absent à l'appel de la fonction ou de la Sub.

Pour tester si l'argument nommé 'relatif' a été indiqué ou omis, on utilise la fonction:
IsMissing(nom de l'argument)

• Si IsMissing(relatif) est True alors l'argument de nom 'relatif' n'a pas été fourni à l'appel de la fonction
• Si IsMissing(relatif) est False alors l'argument de nom 'relatif' a été passé à la fonction

Dans mon cas, je me fiche de sa valeur. Seule sa présence ou son absence m'importe.

Dans la fonction, x est la colonne de la dernière cellule avec une valeur.
Si l'argument est présent, je dois retourner une valeur relative de colonne et j'enlève donc ce qu'il faut à x ( x qui est à ce stade de l'exécution le numéro absolu de la colonne)

Si l'argument est absent, je dois retourner une valeur absolue de colonne et j'enlève donc rien du tout à x ( x qui est déjà à ce stade de l'exécution le numéro absolu de la colonne)


Le qualificateur ParamArray est aussi très intéressant quand on fait une fonction (ou sub) dont le nombre d'arguments peut varier. Exemple: la fonction Concatener d'Excel. Seul Le dernier argument peut être un tableau à dimension indéterminée .

Optional et ParamArray s'excluent mutuellement.

Voir fichier avec deux exemples appliqués sur Feuil1 :

• Function Manque(Optional x1, Optional x2, Optional x3) As String
• Function Coeff_Somme(Coeff As Double, ParamArray TabXrg())

Le code:

Function Coeff_Somme(Coeff As Double, ParamArray TabXrg())
Dim s As Double, xcell, plage

   If IsMissing(TabXrg) Then
      Coeff_Somme = CVErr(xlErrRef)
   Else
      For Each plage In TabXrg
         For Each xcell In plage
            If Not IsError(xcell) Then If IsNumeric("0" & xcell) Then s = s + xcell
         Next xcell
      Next plage
      Coeff_Somme = Coeff * s
   End If
End Function

Function Manque(Optional x1, Optional x2, Optional x3) As String
Dim s
   s = s & " " & IIf(IsMissing(x1), 1, "")
   s = s & " " & IIf(IsMissing(x2), 2, "")
   s = s & " " & IIf(IsMissing(x3), 3, "")
   Manque = Trim(s)
End Function

 

[maroon]

Heee... pour ma part je suis un peu larguer Par contre j'ai télécharger les fichier car j'ai comme l'impression qu'ils vont me servir

 

[maroon]

En me basant sur toutes vos réponses j'ai écris ces lignes de code qui ont l'air de fonctionner et que je mets ici au cas où vous auriez une remarque

Sub Selection()
   Dim plage As Range
   Dim cel As Range
   Dim i As Variant
   Dim derlig As Integer     
   derlig = Columns("A:A").Find("*", Range("A1"), , , xlByRows, xlPrevious).Row
 
   Set plage = Range(Cells(1, 1), Cells(derlig, 1)) 
   For Each cel In plage
        For i = 1 To derlig
            If IsEmpty(Cells(i, 1).Value) = True Then               
                Range(Cells(i, 1), Cells(i, Range("A1").End(xlToRight).Column)).Interior.Color = RGB(255, 78, 127)
    End If
    Next i
    Next cel
  End Sub

 

[TooFatBoy]

Bonjour,

A mon avis, si "derlig" est un entier, il n'y a pas de raison que "i" n'en soit pas également un.

Concernant le calcul de ce "derlig", je ne suis pas certain que ce soit bon et que tu trouves réellement la vraie dernière ligne de ton tableau...
J'ai l'impression que ça ne fonctionne que s'il n'y a rien en-dessous du tableau.

La dernière colonne du tableau doit être la même pour toutes les lignes, donc autant la calculer au départ une fois pour toute et la mettre dans une variable.

Tu peux même définir une "plage", d'une ligne de hauteur, allant de la colonne A à la dernière colonne du tableau, que tu peux en suite décaler avec un offset pour colorier les lignes qui doivent l'être.

Tes deux "For" imbriqués font la même chose, donc le travail effectué est égal au carré du travail à réellement effectuer...


Ça pourrait donner un truc comme ça (au détail près du calcul de la dernière ligne) :

Sub Selection()
    Dim plage As Range, ZoneEnCouleur As Range, cel As Range
    Dim derlig As Integer

    derlig = Columns("A:A").Find("*", Range("A1"), , , xlByRows, xlPrevious).Row
    Set plage = Range(Cells(1, 1), Cells(derlig, 1))
    Set ZoneEnCouleur = Range(Cells(1,1),cells(1 , Range("A1").End(xlToRight).Column))

    For Each cel In plage
        If IsEmpty(Cel) = True Then ZoneEnCouleur.Offset(Cel.row - 1, 0).Interior.Color = RGB(255, 78, 127)
    Next cel
End Sub

 

[Staple1600]

Bonjour le fil

>•mapomme
1) je suis déçu (cf ma blagounette passée inaperçue- cf #51
2) Merci pour ce luxe d'explication
Ce qui me perturbait c'est de ne pas voir par exemple
Optional relatif As Boolean=True

3) Par contre, étudier ton code, alors que je suis à jeun*, ça va être coton.
*: je parle de petit déj: le café est en train de couler à l'heure où j'écris ces lignes, et j'ai pas encore fait ma pâte à pancake )

 

[Staple1600]

Re

•>maroon
Sur mon fichier de test (que je viens de créér)
Ce oneliner fait la même chose que ton code ou celui de Marcel32

Sub Selection3()
ActiveSheet.UsedRange.EntireRow.SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(255, 78, 127)
End Sub

 

[Staple1600]

Re

Rectification.
Ci-dessous un code pour reproduire les conditions de mon test

Sub CreerTEST()
ActiveSheet.Cells.Clear
[A4:I22] = "=REPT(ADDRESS(ROW(),COLUMN(),4),MOD(ROW(),2)=0)": [A4:I22] = [A4:I22].Value
End Sub

Dans ces conditions, seul mon code ne colorie que les cellules vides appartenant au "tableau".

 

[jmfmarques]

Bon nouveau jour de confinement

Je vous suggère de faire vos essais sur des situations telles celle-ci :
voilà de quoi construire une feuille plus représentative de choses pouvant arriver :

With ActiveSheet
  .Range("C1") = "a"
  .Range("X10").Formula = "=IF(E1<>"""", E1,"""")"
  .Range("K100000").Formula = "=IF(E1<>"""", E1,"""")"
  .Range("G123000").Interior.Color = RGB(0, 0, 0)
End With

Garder à l'esprit certaines "habitudes" (formules étirées sans modération, formatages de colonnes ou lignes entières, etc ...) qui peuvent "étendre" les choses au delà encore de ces 100000 (formules) ou 123000 (formatage).
Et bien sûr : sortez votre chrono

 

[mapomme]

Bonjour @Staple1600

je suis déçu (cf ma blagounette passée inaperçue- cf #51

Pas du tout ! Je me suis même instruit sur les différents types de térébenthine et colophane.
Quant à la société DerCol qui est spécialisée dans les utilisations des bourgeons de pin, je trouve que, d'un point de vue de pin, c'est le massacre de ma future progéniture.