XL 2019 Sélectionner dernières cellules non vide sur une ligne (VBA)

[bluesky12000]

Bonjour à tous,

J'ai un problème qui doit être très simple à régler mais je débute en VBA sur Excel.
J'ai un tableau qui va de A1 à H1 et qui s'étend vers le bas au fur et à mesure.
Je souhaite créer un bouton pour supprimer la dernière ligne qui contient des données mais mon code efface uniquement la dernière cellule A au lieu d'aller jusqu'à H

Sheets("Nom de la feuille").Select
Range("A1:H1").End(xlDown).Select
Selection.ClearContents

Merci pour votre aide,

 

[soan]

Bonjour bluesky12000, Staple,

je crois que tu as zappé mon post #11.

---

tu as écrit : « comment savoir que ListObjects(1) est bien égal à ListObjects("Table54") ? »

je ne t'avais pas parlé des ListObjects car sans ton fichier, je ne connaissais pas
le nom de ton tableau structuré ; Staple a eu une très bonne idée d'en parler
quand même, en utilisant l'index 1.

sur une même feuille de calcul, il peut y avoir plusieurs tableaux structurés ;
Worksheets("Feuil1").ListObjects() est la collection de tous les Tableaux structurés
de "Feuil1" : .ListObjects(1) pour le 1er Tableau structuré ; .ListObjects(2) pour
le 2ème ; .ListObjects(3) pour le 3ème ; etc... de la même façon que pour les
index de feuille : Worksheets(1) pour la 1ère feuille de calcul du classeur ;
Worksheets(2) pour la seconde feuille ; Worksheets(3) pour la 3ème ; etc...

conclusion : si tu n'as qu'un seul Tableau structuré sur ta feuille de calcul,
alors celui-ci est forcément ton Tableau structuré nommé "Table54".

soan

 

[soan]

@bluesky12000

pour les Tableaux structurés d'une feuille de calcul, si tu veux connaître
la correspondance des n° et des noms pour "Feuil1", tu peux faire :

Sub Essai()
  Dim i As Byte
  With Worksheets("Feuil1")
    For i = 1 To .ListObjects.Count
      MsgBox "Tableau structuré n° " & i & " : " & .ListObjects(i).Name
    Next i
  End With
End Sub

bien sûr, tu peux éventuellement remplacer Worksheets("Feuil1")
par Worksheets(1) ; si "Feuil1" est bien la 1ère feuille du classeur ... et c'est pas
forcément le cas si y'a d'autres feuilles et que tu l'as déplacée à droite ; ou même
par ActiveSheet si tu lances la sub à partir de la feuille "Feuil1".

au cas où tu voudrais mettre plus de 255 Tableaux structurés sur la
même feuille, pense à mettre : Dim i% au lieu de : Dim i As Byte ;
mais j'pense pas qu't'en auras besoin d'autant !

soan

 

[soan]

@bluesky12000

ajout : fais Ctrl F3 ➯ fenêtre "Gestionnaire de noms"

tu peux y voir tous les nom définis du classeur, y compris les noms de chaque
Tableau structuré ; c'est d'après la colonne « Fait référence à » que tu peux voir
de quelle feuille dépend un Tableau structuré donné ; note que si "Tableau1"
existe déjà sur "Feuil1", tu ne peux pas nommer un autre Tableau structuré
avec le même nom, ni sur "Feuil1", ni sur une autre feuille ; c'est aussi pour
cette raison qu'il est préférable de donner un nom explicite au ListObject
plutôt que simplement "Tableau1" ou "Tableau2".

attention : pour nommer un Tableau structuré, tu ne peux pas le faire dans
le Gestionnaire de noms ; tu dois le faire dans l'onglet "Création" (du ruban),
qui apparaît quand tu sélectionnes une des cellules du Tableau structuré.

soan

 

[soan]

@bluesky12000

je te propose le fichier Excel joint en fin de post.

le Tableau structuré s'appelle "Table54" (comme le tien).

tu peux voir qu'il comporte au départ 5 lignes de données.

Ctrl e ➯ ça te demande si tu veux supprimer la dernière ligne ; clique sur Oui.
(tu auras admiré au passage, n'est-ce pas, le texte sur 2 lignes de la boîte de dialogue ? )

refais cette même manip 4× ➯ y'a plus aucune ligne de données !

attention : fais de nouveau Ctrl e ! vu ? eh oui !

si besoin, tu peux demander une adaptation.
à te lire pour avoir ton avis.

---

si tu arrives à l'adapter au tableau réel de ton vrai fichier : parfait !
sinon : envoie ton fichier sans données confidentielles, et je le ferai
pour toi (gratuitement, car on est sur un forum d'entraide bénévole, sans aucune
rémunération, ni heures supplémentaires payées à 150 % ).

si Staple peut me livrer des crêpes bretonnes et des fars bretons, c'est pas d'refus !
comment ? y'a pas non plus de pots-de-vin ? oh ! que c'est dommage !

c'est pas avec ça que j'vais faire ripaille, hein ?

soan

 

[soan]

@bluesky12000

je te laisse lire mes 4 posts précédents, à partir de mon post #16 ;

bonne lecture, et si tu as d'autres questions, n'hésite pas !

soan

 

[Staple1600]

Re

=>BleuCiel
[précisions]
Ceci dit, je ne vois aucun intérêt à cette débauche de VBA pour faire du "contre-nature"* avec un ListObject.
(* AKA supprimer une ligne ou son contenu)
L'intérêt du tableaux structuré, c'est qu'il s'incrémente automatiquement à chaque ajout de ligne
(et que donc il recopie formules et format)

Spoiler: Voici ci-dessous un petit test pour illustrer mon propos

Sub CREATION_TABLEAU() ' <= MACRO juste pour créer un Tableau
'A EXECUTER ( une seule fois) SUR UNE FEUILLE VIERGE
On Error Resume Next
ActiveSheet.ListObjects(1).Delete
[A1:F1] = "=""ITEM_""&COLUMN()": [A2:F2] = "=COLUMN()&""-""&ADDRESS(ROW(),COLUMN(),4)"
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$F$2"), , xlYes).Name = "Tableau1"
End Sub
Sub test_TABLEAU()
Dim lObj As ListObject
Set lObj = ActiveSheet.ListObjects(1)
Infos_Tableau "Tableau1" '<= juste pour le test
'et laisser le temps à mon café de refroidir ;-)
lObj.ListRows.Add AlwaysInsert:=True
End Sub
Private Sub Infos_Tableau(strTab As String)
Dim infos$
With ActiveSheet.ListObjects(strTab)
infos = "Nom de la feuille où se trouve le tableau: " & .Parent.Name & vbCr
infos = infos & "Nom Tableau: " & .Name & vbCr
infos = infos & "Style appliqué: " & .TableStyle & vbCr
With .DataBodyRange
    infos = infos & "Plage de données du Tableau: " & .Address & vbCr
    infos = infos & "NB Lignes: " & .Rows.Count & " NB Colonnes: " & .Columns.Count & vbCr
End With
End With
MsgBox infos, vbInformation
End Sub

 

[bluesky12000]

@bluesky12000

je te laisse lire mes 4 posts précédents, à partir de mon post #16 ;

bonne lecture, et si tu as d'autres questions, n'hésite pas !

soan

@bluesky12000

je te laisse lire mes 4 posts précédents, à partir de mon post #16 ;

bonne lecture, et si tu as d'autres questions, n'hésite pas !

soan

Merci beaucoup Soan pour toutes tes indications et le temps que tu me dédies

Ton code fonctionne parfaitement, j'ai même ajouté la valeur d'une troisième colonne.
Je n'ai pas encore compris tous les numéros liés à Msgbox mais j'imagine que c'est pour détailler les types de boutons.

J'ai juste eu un bug en écrivant mon propre code sur msg& en écrivant : msg As Long et j'ai compris après que le dollar se référait à String.

 

[bluesky12000]

Re

=>BleuCiel
[précisions]
Ceci dit, je ne vois aucun intérêt à cette débauche de VBA pour faire du "contre-nature"* avec un ListObject.
(* AKA supprimer une ligne ou son contenu)
L'intérêt du tableaux structuré, c'est qu'il s'incrémente automatiquement à chaque ajout de ligne
(et que donc il recopie formules et format)

Spoiler: Voici ci-dessous un petit test pour illustrer mon propos

Sub CREATION_TABLEAU() ' <= MACRO juste pour créer un Tableau
'A EXECUTER ( une seule fois) SUR UNE FEUILLE VIERGE
On Error Resume Next
ActiveSheet.ListObjects(1).Delete
[A1:F1] = "=""ITEM_""&COLUMN()": [A2:F2] = "=COLUMN()&""-""&ADDRESS(ROW(),COLUMN(),4)"
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$F$2"), , xlYes).Name = "Tableau1"
End Sub
Sub test_TABLEAU()
Dim lObj As ListObject
Set lObj = ActiveSheet.ListObjects(1)
Infos_Tableau "Tableau1" '<= juste pour le test
'et laisser le temps à mon café de refroidir ;-)
lObj.ListRows.Add AlwaysInsert:=True
End Sub
Private Sub Infos_Tableau(strTab As String)
Dim infos$
With ActiveSheet.ListObjects(strTab)
infos = "Nom de la feuille où se trouve le tableau: " & .Parent.Name & vbCr
infos = infos & "Nom Tableau: " & .Name & vbCr
infos = infos & "Style appliqué: " & .TableStyle & vbCr
With .DataBodyRange
    infos = infos & "Plage de données du Tableau: " & .Address & vbCr
    infos = infos & "NB Lignes: " & .Rows.Count & " NB Colonnes: " & .Columns.Count & vbCr
End With
End With
MsgBox infos, vbInformation
End Sub

Merci également Staple pour toutes tes infos que je lis attentivement.

En fait pourquoi supprimer des lignes du tableau via VBA ?
Tout simplement car je verrouille ma base de données.
Pour entrer des données il y a un petit formulaire juste à côté pour ajouter des données ou supprimer la dernière ligne. Maintenant je vais regarder comment, via le numéro de ligne, modifier les données

 

[soan]

@bluesky12000

merci pour ton retour !

à propos des numéros des msg, j'ai utilisé ceux-ci :
4 = vbYesNo ; 6 = vbYes ; 48 = vbExclamation

---

pour la variable msg, le type est effectivement String,
car Dim msg$ : idem que Dim msg As String

attention : ne confonds pas msg$ et msg& ! « $ » = String ; « & » = Long
ce sont des caractères de déclaration de type ; deux autres exemples :
« % » = Integer ; « @ » = Currency

soan

 

[Staple1600]

Re

=>bluesky
Comme je le disais, l'interet du tableau structuré, une fois qu'il est mis en place (donc avec ces formules et son format), c'est qu'à chaque ajout de ligne, les formules et le format sont recopiés vers le bas.
Donc si tu supprimes la dernière ligne de ton tableau, tu perds cette fonctionnalité d'auto-incrémentation.

Tu as testé l'exemple du message#22?

L'ajout d'une ligne (ici par macro pour l'exemple) mais cela se fait facilement manuellement recopie bien les formules et les formats.

EDITION: Ci-dessous , le petit plus offert par la fonctionnalité Tableau si on fait une insertion manuelle

 

[bluesky12000]

Re

=>bluesky
Comme je le disais, l'interet du tableau structuré, une fois qu'il est mis en place (donc avec ces formules et son format), c'est qu'à chaque ajout de ligne, les formules et le format sont recopiés vers le bas.
Donc si tu supprimes la dernière ligne de ton tableau, tu perds cette fonctionnalité d'auto-incrémentation.

Tu as testé l'exemple du message#22?
L'ajout d'une ligne (ici par macro pour l'exemple) mais cela se fait facilement manuellement recopie bien les formules et les formats.

Re

=>bluesky
Comme je le disais, l'interet du tableau structuré, une fois qu'il est mis en place (donc avec ces formules et son format), c'est qu'à chaque ajout de ligne, les formules et le format sont recopiés vers le bas.
Donc si tu supprimes la dernière ligne de ton tableau, tu perds cette fonctionnalité d'auto-incrémentation.

Tu as testé l'exemple du message#22?

L'ajout d'une ligne (ici par macro pour l'exemple) mais cela se fait facilement manuellement recopie bien les formules et les formats.

EDITION: Ci-dessous , le petit plus offert par la fonctionnalité Tableau si on fait une insertion manuelle
Regarde la pièce jointe 1092800

Oui merci Staple, j'ai testé le code et il y a plein de choses intéressantes à récupérer dedans.

Concernant mon cas de figure, mon code copie la valeur de la donnée à insérer dans la colonne voulue.

Ensuite j'utilise par exemple :
Range("B1").End(xlDown).Offset(1, 0).Select
et cela crée bien une ligne intégrée à mon tableau avec toutes les formules conservées.

 

[Staple1600]

Re

=>Bluesky
Range("B1").End(xlDown).Offset(1, 0).Select
Pas chez moi, si le tableau est bien un ListObject
Pas d'ajout de nouvelle ligne.

Tu peux faire une copie d'écran de ton tableau?

 

[Staple1600]

Re

J'ai testé avec un tableau structuré en A1:H10
Le code ci-dessous ajoute bien une ligne en bas de tableau

Sub test_ok()
Ajout_Ligne ActiveSheet.ListObjects(1)
End Sub
Private Sub Ajout_Ligne(Tableau As ListObject)
'ajoute une ligne en bas du tableau designé
Tableau.ListRows.Add
End Sub

 

[bluesky12000]

Re

=>Bluesky
Range("B1").End(xlDown).Offset(1, 0).Select
Pas chez moi, si le tableau est bien un ListObject
Pas d'ajout de nouvelle ligne.

Tu peux faire une copie d'écran de ton tableau?

Voici mon fichier, j'ai juste remarqué un bug, dans le cas de figure ou je supprime avec mon macro la ligne 2, alors la nouvelle ligne créée irait en ligne 3 au lieu de 2.

 

[Staple1600]

Re

En attendant de regarder ta PJ
Regardes cette copie écran
(la 1ère partie du l'image, c'est après ton code avec xlDown
(la 2ième partie, c'est après mon code)