Boucle pour supprimer 1er caractère dans une colonne

Bonjour le forum,

j'ai la macro ci-dessous qui a ajouté ' en 1er caractère dans la colonne i

Sub Inserrer()
Dim k As Integer
Dim Lig As Long
Lig = 3                            'première ligne à vérifier
Do While Not IsEmpty(Range("A" & Lig))            'cherche dernière CellNonVide de la colonne A
    Lig = Lig + 1
Loop
k = 3
    While k <= Lig - 1                    'boucle de 3 à Lig
        Cells(k, 9) = ("'" & Cells(k, 9))        'ajoute ' en début de cellule de la colonne I
        Cells(k, 1) = k                    'numérote de 1 à MaxLigNonVide de la colonne A
           k = k + 1
    Wend
MsgBox "La première ligne vide colonne A est la ligne : " & Lig
If MsgBox("Y aller ?", vbYesNo + vbQuestion, "Confirmation ?") = vbYes Then
    ActiveSheet.Cells(Lig, 1).Select
End If
End Sub

mais là, je voudrais supprimer ce 1er caractère dans la colonne i, je n'arrive pas a trouver comment modifer "Cells(k, 9) = ("'" & Cells(k, 9))"

si ça peut aider cette macro est dans un fichier macro complémentaire xlam et je ne sais pas si ça a un rapport mais si je supprime à la main ce caractère ' il revient aussitôt comme dans le classeur1.xlsx ci-joint

Pourriez-vous m'aider

Merci d'avance

Bonjour,

à tester,

Sub test()
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
  If Cells(i, "A") <> "" Then
    Cells(i, "A") = Right(Cells(i, "A"), Len(Cells(i, "A")) - 1)
  End If
Next i
End Sub

bonjour

salut au passage Isabelle

sans VBA, sans formule

menu Données, obtenir "de plage"

"charger"

3 clics en tout, sans utiliser le clavier

inutile de transformer quoi que ce soit. Power Query a compris tout seul (par IA ? ) que les ' sont des artefacts parfois nécessaires dans Excel et qu'on peut les supprimer

mais si tu veux on peut en rajouter, jette un oeil aux menus de P Query au passage

amitiés à tous

Bonjour Isabelle, jmd, le forum,

Merci pour vos réponses

Isabelle, j'ai testé la macro, ça n'a rien changé ou alors je commence a avoir le doute que le ' se remet automatiquement comme quand je le supprime à la main, il doit le faire assez vite car je ne vois rien dérouler

jmd, j'ai essayé de suivre scrupuleusement ce que tu dis, je n'ai pas obtenir "de plage" mais plus loin tu parles de Power Query, je l'ai tapé dans l'aide en ligne et ça m'a proposé de l'activer, maintenant j'ai un menu "Power Pivot" dedans en fouillant j'ai un bouton Gérer et une fois lancé j'ai une fenêtre avec un bouton Obtenir des données externes, je ne pense pas que je sois sur la bonne route ^^

du coup je reviens sur le menu Données, et dans le sous menu Outils de données je retrouve le bouton Gérer le modèle de données, comment puis-je deactiver le menu Power Pivot ?

j'ai aussi regardé ton fichier joint, il me plait beaucoup car le problème du début c'est que je voulais empecher Excel de mettre un lien mailto sur toutes les cellules de ma colonne i contenant nom.prénom@titi.fr, on m'a conseillée de mettre ' devant chaque adresse et vu ma longue liste l'ajout de ' a été fait par une macro, mais mon nouveau probleme est que si je copie colle vers ma messagerie une adresse avec ' devant il me faut l'enlever, voila le but de ma demande

alors que dans ton fichier tu n'as plus de ' mais en plus Excel ne mets plus de lien mailto, j'aimerai savoir comment je dois faire et oui si c'est possible sans macro ça m'arrange, inutile de complexer ça l'est déjà bien assez

failli oublié de dire je n'ai pas trouvé le menu P Query mais j'ai souvent lu des posts en parler

Cordialement

edit pour info

je suis allée voir d'autre post correspondant au mien et j'ai trouvé cette macro ci-dessous que j'ai adaptée a ma colonne i et au caractere ' ca n'a rien changé

Sub nom()
Columns("i:i").Select
    Selection.Replace What:="'", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

Bonjour,

à l'ancienne pour les ' récalcitrantes :

    Dim c As Range, v As Variant
    For Each c In Selection
        If c.PrefixCharacter <> "" Then
            v = c.Value: c.Clear: c.Value = v
        End If
    Next c

par contre obligé de faire sauter les formats aussi.

Tu pourrais plus simplement ajouter une espace devant à la place de l'apostrophe, plus facile à traiter ensuite.

Ou bien dans les options de correction automatique décocher Remplacer Adresses internet et réseau avec des liens hypertexte.

Mais c'est pour toute l'appli, pas seulement le classeur.

eric

re,

cette colonne contient des valeurs numérique ou texte ?

Bonjour Eric,

Merci, c'est très efficace et pas grave si ça supprime le format, il est facile a recopier

mon problème c'était aussi que j'avais 4 ou 5 cellules vides et que la macro pour ajouter ' a toutes les cellules de la colonne i a mis 1 ' dans chaque cellule vide, et que j'avais tenté de supprimer la cellule mais que le ' revenait

Merci à toute et tous

Ps si vous revenez dire quelque chose dans ce post (que je passe en résolu) je ne suis pas bien loin, pour éviter de vous laisser sans réponse

re Isabelle,

les cellules contiennent des adresses mail qui devenait des liens hypertextes mailto, ça me gênait un peu, voila pourquoi j'ai mis des ' devant chaque adresse, ça remonte y'a quelques semaines mais à l'usage les ' me gêne aussi mais impossible a supprimer sauf avec la boucle d'Eric qui a tout nettoyé même la police et les bordures que je vais remettre facilement

Cordialement

re,

les cellules contiennent des adresses mail qui devenait des liens hypertextes mailto

ha je comprend mieux,

la prochaine fois utilise le caractère ~ --> CAR(126)

il est rarement utilisé et plus facile à enlever.

bonne continuation!

Isabelle

je viens de tester ~ Excel ne le voit pas et il mets un lien mailto, du moins en version 2016

j'essayerai ce soir chez moi en excel 2010

je crains qu'il n'y ai pas beaucoup de solution que de décocher la correction automatique des liens hypertexte et de la réactiver en cas de besoin

Cordialement

Comme dit précédemment, met une espace devant, ça suffit.

Tout autre caractère sera considéré comme faisant partie de l'adresse.

eric

Sherana a écrit :

jmd, j'ai essayé de suivre scrupuleusement ce que tu dis, je n'ai pas obtenir "de plage" mais plus loin tu parles de Power Query, je l'ai tapé dans l'aide en ligne et ça m'a proposé de l'activer, maintenant j'ai un menu "Power Pivot" dedans en fouillant j'ai un bouton Gérer et une fois lancé j'ai une fenêtre avec un bouton Obtenir des données externes, je ne pense pas que je sois sur la bonne route ^^

du coup je reviens sur le menu Données, et dans le sous menu Outils de données je retrouve le bouton Gérer le modèle de données, comment puis-je deactiver le menu Power Pivot ?

re à tous

ne désactive pas P Query ni PPivot (il est curieux que PPivot te soit fourni gratuitement)

ce sont des fonctionnalités très puissantes d'Excel

je te conseille fortement d'apprendre P Query, à petite dose

C'est LE truc qui sait transformer les données (d'Excel et même d'autres données)

On dit "ETL"

sans doute un des plus puissants au monde

P Pivot : je ne l'utilise plus depuis Power BI Desktop (gratuit et génial)

amitiés

Bonjour,

Pour information, un moyen d'empêcher la création d'un lien mailto dans une procédure.

Public Sub AutoFormatHyperlinks()
Dim bProperty As Boolean
    bProperty = Application.AutoFormatAsYouTypeReplaceHyperlinks
    Application.AutoFormatAsYouTypeReplaceHyperlinks = False
    'code
    '
    '
    Application.AutoFormatAsYouTypeReplaceHyperlinks = bProperty
End Sub

Bonjour JMD, le forum,

Je n'ai pas réussi a activer P Query, si tu pouvais STP m'expliquer comment faire

Excel ne me propose que P Pivot, je suis au boulot ça pourrait expliquer pourquoi j'ai P Pivot ils ont surement payé une licence !

Ok je ne désactive pas P Pivot et a temps perdu j'essayerai de comprendre a quoi il sert et comment m'en servir, déjà je suis loin de connaitre et maitriser les fonctions, encore moins le Vba, j'ai l'impression que ces 2 outils sont bien trop pointu pour mon usage, j'ai un besoin d'Excel plus limité que mon envie, j'ai surtout connu Excel 4 avec les macro-commandes et l'arrivée de VBA en Excel 5 m'a beaucoup perturbé même si je vois le Vba bien + puissant ^^

Merci beaucoup pour tes conseils avisés

Re,

P Query n'existe pas, c'est Power Query et il est intégré à Excel 2016.

Cela correspond dans le Ruban, Données, à Récupérer et transformer.

Tu peux désactiver Power Pivot, si tu n'en as pas l'utilité.

Un lien pour t'informer des fonctionnalités de Power Query, dont tu n'as besoin pour le sujet en cours :

https://support.office.com/fr-fr/article/aide-de-microsoft-power-query-pour-excel-2b433a85-ddfb-420b-9cda-fe0e60b82a94?ui=fr-FR&rs=fr-FR&ad=FR

Cordialement.

Bonjour Jean-Eric,

Merci pour cette info

Je ne maitrise pas les procédures mais en cherchant sur le net et en suivant https://www.excel-pratique.com/fr/vba/evenements_classeur j'ai en ouvrant un fichier.xlsm afficher une boite de dialogue affichant la date et en cliquant sur ok la date c'est mise en A1

j'ai essayé de mettre ta procédure a la place

Private Sub AutoFormatHyperlinks()
Dim bProperty As Boolean
    bProperty = Application.AutoFormatAsYouTypeReplaceHyperlinks
    Application.AutoFormatAsYouTypeReplaceHyperlinks = False
    'code
    '
    '
    Application.AutoFormatAsYouTypeReplaceHyperlinks = bProperty
End Sub

et si j'ouvre ce fichier.xlsm l'ecris une adresse mail et en validant elle ne devient pas un lien mailto mais si j'ecris une 2 eme adresse de la même façon elle devient un lien mailto

Peux-tu me guider STP

Jean-Eric,

Je réponds un peu en décalé, désolé ^^

je voulais bien dire Power Query pour P Query et gràce a ta réponse, j'ai effectivement "Récupérer et transformer" dans le ruban, Merci

Je file voir le lien que tu m'as fourni, je reviendrai plus tard pour dire où j'en suis

Cordialement

Re,

Je ne sais pas vraiment ce que tu veux faire, mais essaie ceci.

A l'ouverture du fichier, on désactive les liens et on les réactive quand on quitte le classeur.

Attention ! la variable bProperty est déclaré en Public dans Module1.

A te relire.

Cdlt.

Private Sub Workbook_Open()
    bProperty = Application.AutoFormatAsYouTypeReplaceHyperlinks
    Application.AutoFormatAsYouTypeReplaceHyperlinks = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.AutoFormatAsYouTypeReplaceHyperlinks = bProperty
End Sub

Re Jean-Eric

A l'origine, mon problème était que dans une colonne de mon classeur, j'ai des adresses mail qui se sont transformés en lien mailto, ce qui est normal, mais mon classeur me sert de base a tout un tas de chose je copie colle vers EzPublish, vers Outlook, vers Word et PowerPoint mais aussi vers d'autre feuille Excel..Etc

ce qui me genait c'était de ne pas pouvoir selectionner 1 cellule sans que le lien mailto s'active et aussi que je n'ai pas besoin de coller un lien, j'ai voulu a cet époque masquer et j'ai mis une ' devant chaque adresse mail et comme j'en avais beaucoup ça a été fait par une boucle ci-dessous

Sub Inserrer()
Dim k As Integer
Dim Lig As Long
Lig = 3                                     'première ligne à vérifier
Do While Not IsEmpty(Range("A" & Lig))      'cherche dernière CellNonVide de la colonne A
    Lig = Lig + 1
Loop
k = 3
    While k <= Lig - 1                      'boucle de 3 à Lig
        Cells(k, 9) = ("'" & Cells(k, 9))   'ajoute ' en début de cellule de la colonne I
        Cells(k, 1) = k                     'numérote de 1 à MaxLigNonVide de la colonne A
           k = k + 1
    Wend
MsgBox "La première ligne vide colonne A est la ligne : " & Lig
End Sub

A l'utilisation il s'est avéré que ' devant chaque adresse était copier et collé vers mes autres destinations et que je devais l'enlever mais j'ignore pourquoi car même à la main si j'enlevais la ' de 'toto@url.fr et que je faisais Entrée la ' revenait aussitôt d'où le début de mon post

on m'a proposé le code ci-dessous pour réellement effacer les ' dans toute ma colonne

Dim c As Range, v As Variant
    For Each c In Selection
        If c.PrefixCharacter <> "" Then
            v = c.Value: c.Clear: c.Value = v
        End If
    Next c

ça a été efficace, j'ai remis la police et la tailles des caractères ainsi que les bordures et je me suis résous à cocher/décocher dans les options, vérification automatique, Adresses Internet et réseaux avec liens hypertexte

Maintenant ce que tu proposes, si j'arrive à le mettre en place serait bien venu à condition de n'affecter qu'une seule feuille de mon classeur et mettre le code dans le module1 de mon fichier "MesMacrosComplémentaires.xlam" pour conserver mon fichier de travail sous la forme .xlsx

Edit:

si c'est possible en MFC il n'est pas indispensable de passer par du VBA ^^

re à tous

Maintenant que tu as installé P Query, merci à Jean-Eric au passage,

tu peux essayer ma manoeuvre

"récupérer ou Obtenir (selon ta version)

de classeur ou de répertoire (je ne suis pas certain)

navigue vers ton répertoire

bonne journée à tous