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

maroon · 27 Mars 2020

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:

VB:

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!

Staple1600 · 28 Mars 2020

Re, Bonjour patrictoulon

Toujours basé sur le fichier exemple (cf message#18)

VB:

Sub Colorier_Lignes_Vides_III()
[Indicateurs].Resize(, [A1].CurrentRegion.Columns.Count).Offset(1).SpecialCells(4).Interior.Color = RGB(255, 133, 99)
End Sub

patricktoulon · 28 Mars 2020

je viens d'ouvrir le fichier
c'est une blague elle sont toute remplie ces cells du tableau

allez j'me barre avant d'attraper la coronaconnerie

Staple1600 · 28 Mars 2020

Re

Non, elles ne sont pas toutes remplies.
Sinon le code que je propose ne mettrait pas de jolies couleurs dedans.

PS: Et ce depuis le message#21

mapomme · 28 Mars 2020

Bonjour à tous @maroon

, @Marcel32

, @Staple1600

, @jmfmarques

, @patricktoulon

, @mapomme

Cette nuit , on passe à l'heure d'été. N'oubliez pas de mettre le réveil à 2h00 du matin pour l'avancer d'une heure.

Bon, j'ai vu le titre de la discussion tardivement et j'avoue ne pas avoir lu la majorité des messages.

Cependant ça m'a rappelé une fonction que j'avais faite pour trouver la dernière ligne d'une colonne.
Les micros n'étant pas infaillibles, cette fonction s'est évaporée dans les limbes à la mort de mon dernier PC. Snif!

Cette fonction permettait de trouver la dernière cellule non vide d'une plage de cellules contenue dans une seule colonne (donc à fortiori d'une colonne entière) :

que les lignes soient masquées ou non
avec ou sans tableau structurée
avec un filtre actif ou non
avec possibilité de retourner un numéro de ligne relatif ou absolu

J'ai donc reconstruit cette fonction pour retourner la dernière colonne d'une plage :

la plage est soit une ligne entière soit une partie de ligne (cellules contigües au sein d'une même ligne)
la fonction renvoie le numéro de la dernière colonne non vide
si la ligne est vide, alors on renvoie le nombre 0
les cellules contenant la chaine "" sont considérées comme étant vides (c'est souvent ce qu'on veut)
un paramètre optionnel appelé Relatif modifie le comportement de la fonction. S'il est absent, on retourne le numéro absolue de la colonne. S'il est présent, on retourne le numéro relatif de la colonne par rapport à la première colonne de la plage. Évidemment, si la plage est une ligne complète, alors le numéro "relatif" est égal au numéro "absolu".
la fonction prend en compte les tableaux structurés ou non
la fonction prend en compte les colonnes masquées
si la plage n'est pas contenue dans une seule ligne, alors la fonction retourne une erreur

En fait il y a trois fonctions:

La fonction principale:
Function DerColOccup (xrg As Range, Optional relatif)
qui retourne le numéro (absolu ou relatif) de la dernière colonne non vide de la plage xrg

Deux sous-fonctions:
Function DerColOccupLettre (xrg As Range, Optional relatif)
qui retourne le numéro (absolu ou relatif) de la dernière colonne non vide de la plage xrg contenant un texte (rappel le texte "" est considéré comme vide)

et

Function DerColOccupNombre (xrg As Range, Optional relatif)
qui retourne le numéro (absolu ou relatif) de la dernière colonne non vide de la plage xrg contenant un nombre

Feuil n°1 : présente l'anomalie quand on utilise End() avec des colonnes masquées
Feuil n°2 : présente l'utilisation des fonctions sur une feuille de calcul
Feuil n°3 : présente l'utilisation de la fonction dans une macro VBA sur une feuille avec des colonnes masquées

En ce qui me concerne, le confinement est propice à la logorrhée épistolaire

.

mapomme · 28 Mars 2020

Re,

Pour ceux qui n’ouvrent pas les fichiers (et c'est bien dommage avec le temps que j'ai passé dessus quoique du temps, on en à revendre), voici le code des fonctions:

VB:

Option Explicit

Function DerColOccup(xrg As Range, Optional relatif)
Dim N, C
   If IsMissing(relatif) Then C = DerColOccupLettre(xrg) Else C = DerColOccupLettre(xrg, 1)
   If IsMissing(relatif) Then N = DerColOccupNombre(xrg) Else N = DerColOccupNombre(xrg, 1)
   If IsNumeric(C) And IsNumeric(N) Then
      DerColOccup = IIf(C > N, C, N)
   ElseIf IsError(C) Then
      DerColOccup = C
   Else
      DerColOccup = N
   End If
End Function

Function DerColOccupLettre(xrg As Range, Optional relatif)
Dim colinf, F As Worksheet, base&

   Set F = xrg.Parent
   If xrg Is Nothing Then DerColOccupLettre = CVErr(xlErrRef): Exit Function
   If xrg.Rows.Count > 1 Then DerColOccupLettre = CVErr(xlErrRef): Exit Function

   On Error Resume Next
   colinf = Application.Match(String(255, "z"), xrg)
   Do
      If IsError(colinf) Then
         colinf = 0
         Exit Do
      Else
         If xrg(colinf) = "" Then
            If colinf = 1 Then
               colinf = 0
               Exit Do
            Else
               colinf = Application.Match(String(255, "z"), xrg.Resize(, colinf - 1))
            End If
         Else
            Exit Do
         End If
      End If
   Loop
   If colinf <> 0 Then DerColOccupLettre = colinf + IIf(IsMissing(relatif), xrg.Column - 1, 0) Else DerColOccupLettre = 0
End Function

Function DerColOccupNombre(xrg As Range, Optional relatif)
Dim colinf, F As Worksheet, base&

   Set F = xrg.Parent
   If xrg Is Nothing Then DerColOccupNombre = CVErr(xlErrRef): Exit Function
   If xrg.Rows.Count > 1 Then DerColOccupNombre = CVErr(xlErrRef): Exit Function

   On Error Resume Next
   colinf = Application.Match(1E+99, xrg)
   If IsError(colinf) Then colinf = 0
   If colinf <> 0 Then DerColOccupNombre = colinf + IIf(IsMissing(relatif), xrg.Column - 1, 0) Else DerColOccupNombre = 0
End Function

Staple1600 · 29 Mars 2020

Re

•>mapomme
Merci pour le code "direct access"

Jolies fonctions.
Mais je reste avec mon oneliner pour ce qui du gribouillage/coloriage

PS1: J'ai téléchargé le fichier du demandeur, parce qu'on arrêtait pas de me dire que mon code ne fonctionnait pas

(c'est pour cela le

? )
PS2: Tu te dis Bonjour à toi-même désormais ?

mapomme · 29 Mars 2020

Staple1600 à dit:
PS2: Tu te dis Bonjour à toi-même désormais ?

Depuis le confinement, je me sens abandonné (les gens ne veulent même plus télécharger mes fichiers tellement ils craignent le virus) alors quand je me salue, ça fait du bien; je sais que quelqu'un pense à moi...

Le

c'était pour la feuille1 de mon fichier que tu n'as pas ouvert...

maroon · 29 Mars 2020

Bonjour à tous,

Merci de vous pencher sur mon problème!

Les choses sont allées un peu vites pour moi

donc je regarde tout ça!! A toute...

Staple1600 · 29 Mars 2020

mapomme à dit:
Le c'était pour la feuille1 de mon fichier que tu n'as pas ouvert...

Bonjour le fil

•>mapomme

maroon · 29 Mars 2020

Bonjour à tous et merci pour votre aide!!

@Marcel 32

VB:

Cells(15, Cells(15, 1).SpecialCells(xlLastCell).Column)

met en couleur la cellule "dernière colonne utilisée +2" de la ligne 15 (je rectifie / à ce que j'avais dit dans mon msg#5)

Code:

ActiveCell.SpecialCells(xlLastCell).Column

Je ne suis pas arrivé à le faire fonctionner tel quel mais en retirant le ".column" cette ligne met en couleur la cellule qui est à l'intersection de la "dernière colonne utilisée +2" et de la "dernière ligne utilisée +2"

Code:

Range("A" & Cells(15, 1).SpecialCells(xlLastCell).Column)

met en couleur la première cellule de la ligne 8! Je comprends pas ça vient peut-être de moi

Code:

Range(Cells(15, 1), Cells(15, Cells(15, 1).SpecialCells(xlLastCell).Column))

C'est bizarre on aurait dit que ce code prend en compte des colonnes qui étaient occupées et qui ont été effacées du coup il met en couleur par ex. jusqu'à la colonne 20 alors que qu'il n'y a plus que 10 colonnes! J'ai pas trop compris le message de @jmfmarque (je suis débutant) mais j'ai l'impression que c'est de ce qu'il parlait.

Code:

Range(Cells(15, 1), Cells(15, Range("A1").End(xlToRight).Column))

Fonctionne pour moi, merci!!

@Staple1600

Code:

Range(Cells(15, 1), Cells(15, Columns.Count).End(xlToLeft))

met en couleur la première cellule de la ligne 15. On en a parlé je crois que ça prend en compte le nombre de colonnes de la ligne 15 et pas de la feuille

Code:

Sub Colorier_Lignes_Vides()
Dim X
Couleurs = Array(vbRed, vbMagenta, vbGreen, vbBlack, vbYellow, vbCyan, vbOrange)
Randomize 1600
X = Application.RandBetween(0, 6)
[Indicateurs].Resize(, ActiveSheet.UsedRange.Columns.Count).SpecialCells(4).Interior.Color = Couleurs(X)
End Sub

Fonctionne pour moi et en plus prend en compte toutes les lignes vides!

@patricktoulon

Code:

Sub test()
Dim ligne&
ligne = 15
MsgBox Range(Cells(ligne, 1), Cells(ligne, Columns.Count).End(xlToLeft)).Address
End Sub

met en couleur la première cellule de la ligne 15 (après modification)

Code:

Sub test2()
Dim cel As Range
Set cel = [A15]
MsgBox Range(cel, Cells(cel.Row, Columns.Count).End(xlToLeft)).Address
End Sub

Idem

Code:

Sub test5() 'en arriere
Set montableau = Range("a1:g25")
With montableau
MsgBox .Find("*", after:=.Cells(.Cells.Count), SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Address
End With
End Sub

me renvoie bien l'adresse de la dernière cellule utilisée mais je n'ai pas réussi à l'utilisée pour faire ce que je voulais.

@mapomme merci pour ce fichier! Je n'ai pas trouvé le moyen de l'appliquer à ce que je voulais faire en revanche il est plein d'informations qui vont me servir sous peu je pense...

@jmfmarques merci pour ces informations et mises en gardes!

maroon · 29 Mars 2020

@Staple1600 Est-ce je dois utiliser cette fonction telle quelle dans d'autres fichiers?

VB:

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

Je ne comprends pas à quoi correspond [Indicateurs]?

Staple1600 · 29 Mars 2020

Bonsoir

•>maroon
C'est bien ton fichier?
Parce ce que Indicateurs est une plage nommée
(mais pas par moi, c'était dans ta PJ)

Pour voir les plages nommées
CTRL+F3

TooFatBoy · 29 Mars 2020

maroon à dit:
C'est bizarre on aurait dit que ce code prend en compte des colonnes qui étaient occupées et qui ont été effacées du coup il met en couleur par ex. jusqu'à la colonne 20 alors que qu'il n'y a plus que 10 colonnes! J'ai pas trop compris le message de @jmfmarque (je suis débutant) mais j'ai l'impression que c'est de ce qu'il parlait.

C'est effectivement ce qui est expliqué dans les messages #8 et #19.

maroon · 29 Mars 2020

Marcel32 à dit:
C'est effectivement ce qui est expliqué dans les messages #8 et #19.

Ok j'avais pas bien compris c'est exactement ça!

maroon · 29 Mars 2020

Staple1600 à dit:
Bonsoir

•>maroon
C'est bien ton fichier?
Parce ce que Indicateurs est une plage nommée
(mais pas par moi, c'était dans ta PJ)
Pour voir les plages nommées
CTRL+F3

Oups! j'avais pas fait le lien

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

XLDnaute Junior

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

Pièces jointes

XLDnaute Junior

XLDnaute Barbatruc

XLDnaute Junior

XLDnaute Junior

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Junior

XLDnaute Junior

Discussions similaires

Partager cette page

Statistiques des forums