Note: The other languages of the website are Google-translated. Back to English
Se connecter  \/ 
x
or
x
INSCRIPTION  \/ 
x

or

Comment trier automatiquement la colonne par valeur dans Excel?

Par exemple, vous avez une table d'achat comme capture d'écran de gauche affichée. Maintenant, vous voulez que la colonne Prix soit triée automatiquement lorsque vous entrez un nouveau nombre / prix dans cette colonne, comment pouvez-vous le résoudre? Ici, je présente une macro VBA pour vous aider à trier automatiquement une colonne spécifique par valeur dans Excel.

Colonne de tri automatique par valeur avec VBA


Colonne de tri automatique par valeur avec VBA

Cette macro VBA triera automatiquement toutes les données dans une colonne spécifique dès que vous entrez de nouvelles données ou que vous modifiez la valeur dans la colonne dans Excel.

1. Cliquez avec le bouton droit sur le nom de la feuille actuelle dans le Barre d'onglets de la feuille, puis cliquez sur le Voir le code dans le menu contextuel.

2. Dans la boîte de dialogue d'ouverture de Microsoft Visual Basic pour Application, collez le code de macro VBA suivant dans la fenêtre d'ouverture.

VBA: Colonne de tri automatique dans Excel

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Range("B1").Sort Key1:=Range("B2"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub

Notes: Dans le code vba ci-dessus, B: B signifie qu'il triera automatiquement la colonne B, B1 est la première cellule de la colonne B, B2 est la deuxième cellule de la colonne B et vous pouvez les modifier en fonction de vos besoins.

3. Revenez ensuite à la feuille de calcul, lorsque vous entrez un nouveau nombre dans la colonne Prix ou que vous modifiez des prix existants, la colonne Prix sera automatiquement triée par ordre croissant.

Notes: lors de la saisie d'un nouveau numéro dans la colonne Prix, vous devez saisir le numéro dans la première cellule vide sous les numéros d'origine. S'il y a des cellules vides entre le nouveau numéro entré et les numéros d'origine ainsi que des cellules vides entre les numéros d'origine, cette colonne ne sera pas triée automatiquement.


Démo: tri automatique des colonnes par valeur avec VBA dans Excel


Kutools pour Excel comprend plus de 300 outils pratiques pour Excel, gratuits à essayer sans limitation en 30 jours. Téléchargez et essayez gratuitement maintenant!

Triez facilement par fréquence d'occurrences dans Excel

Kutools pour Excel Tri avancé L'utilitaire prend en charge le tri rapide des données par longueur de texte, nom de famille, valeur absolue, fréquence, etc. dans Excel. Essai gratuit complet de 30 jours!
annonce trier par fréquence 2


Les meilleurs outils de productivité de bureau

Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée...
  • Fusionner les cellules / lignes / colonnes sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... Empêcher les cellules en double; Comparer les gammes...
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.
onglet kte 201905

Office Tab apporte une interface à onglets à Office et simplifie considérablement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
bas de cabine
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Maurits · 3 months ago
    Love it. Works for me.

    But when I repeat the exact same steps at another Excel file, and enter a number in the colomn for it to autosort, Excel closes. 
  • To post as a guest, your comment is unpublished.
    Steve_B · 5 months ago
    Hi,
    This is extremely useful.
    Is there a way to expand the formula to cover multiple columns? For example, to sort data based on values on first, column B, and then column C?
    I would really appreciate any solutions!
  • To post as a guest, your comment is unpublished.
    Steve · 5 months ago
    Hi,
    This is extremely useful.
    Is there a way to expand the formula to cover multiple columns? For example, to sort data based on values on first, column B, and then column C?
    I would really appreciate any solutions!
  • To post as a guest, your comment is unpublished.
    Zoudan · 10 months ago
    Amazing thanks!!!

  • To post as a guest, your comment is unpublished.
    Dan · 1 years ago
    So this seems to work when the data is manually entered but doesn't work when it is a table that repopulates from another file....is there any way to do that?
  • To post as a guest, your comment is unpublished.
    edwin · 1 years ago
    Hi, it arrange the entire row but I have some link on cells into specific folder which is left behind after sort.
  • To post as a guest, your comment is unpublished.
    kerry · 2 years ago
    This has been really helpful but I have 2 rows of headers (rows 1 and 2) and when I run the VBA it takes the second row to the bottom of the dates
    how do I stop this?
    • To post as a guest, your comment is unpublished.
      kellytte · 2 years ago
      Hi kerry,

      There is a VBA that will automatically sort cells in the Column igonring the first two header cells.

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim xIntRC As Integer
      Dim xURg As Range
      On Error Resume Next
      If Not Intersect(Target, Range("B:B")) Is Nothing Then
      If Not Intersect(Target, Range("B1:B2")) Is Nothing Then Exit Sub
      Set xURg = ActiveSheet.UsedRange
      xIntRC = xURg.Rows.count
      Range("B2:B" & xIntRC).Sort Key1:=Range("B3"), _
      Order1:=xlAscending, Header:=xlYes, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
      End If
      End Sub
      • To post as a guest, your comment is unpublished.
        javad · 1 years ago
        Hi
        this code solve my problem. but i need to sort column B & with this Just change column A, not all . help plz
      • To post as a guest, your comment is unpublished.
        Tilt · 1 years ago
        Hello, I am not having any luck with the code directly above from "kellytte" for sorting cells in the Column ignoring the first two header cells. Can anyone help me? Thanks.
  • To post as a guest, your comment is unpublished.
    Paula · 2 years ago
    May I ask if there is a way to use this VBA code if there is a formula in the column that you want to be the main sort.

    i.e. if A2 = the sum of f2 + g2+ h2 and column A needs to be sorted on the total sum? (I can't get VBA to work if formula in the cells)

    Any assistance would be kindly appreciated as hope to use it for ranking races when the winner is the one with the total of three racing events.

    Thank You
    • To post as a guest, your comment is unpublished.
      Stan · 1 months ago
      For me it worked to change this line

      If Not Intersect(Target, Range("B:B")) Is Nothing Then

      I think the key is to set the Target Range to the fields that change, and keep the sort on the data with the summed results.
    • To post as a guest, your comment is unpublished.
      NABILA ISMAIL · 2 years ago
      i also want to know solution for Paula problem. please share
  • To post as a guest, your comment is unpublished.
    Bekir · 2 years ago
    Hi,

    How can I make this macro start after I fill all data to the specific column?
    I want the listing macro start after I fill empty boxes. Because when I use this, it changes the place suddenly.
    I need to start this macro after I filled the last data of that column (20 datas to be exact)
  • To post as a guest, your comment is unpublished.
    Rhea · 3 years ago
    Hello, I am an intermediate Excel user. I am wondering if someone could help direct me for what I am trying to accomplish. Here is what I am trying to do: Auto-Sort with Custom Order.

    A little about my spreadsheets: I have one sheet (named: Sheet1) that lists 16 columns (A through P) and currently 19 rows (this number will increase as more data is entered). In the last column (column P) all cells have a drop down menu of options (located and pulled from sheet two (named "BackEnd"). The drop down selections are color-coded (using conditional formatting rules).

    What I am hoping to do: As a user enters data in a new row, as soon as they are finished selecting an option from the drop down menu in column P, Excel will auto-sort the new row to a specific order. I have been able to create VBA code for auto-sorting in ascending and descending order, but I have not been able to create a code for auto-sorting the data in a specific order. The order I would like to have the data sorted into is:

    Unresponsive
    Not Interested
    Interested
    Pre-Screened Not Qualified:
    Pre-Screened Qualified
    Application Pending
    Application Approved
    Initial Assessment:
    Scope of Work:
    Home Repairs in Progress
    On Hold:
    Complete
    Other:

    Is this possible? Any help is appreciated! Thank you!
  • To post as a guest, your comment is unpublished.
    Stephen · 3 years ago
    While this is a fantastic code, I would like to know if anyone could help me get it go from ascending to descending? In other words, I'm looking for it to be the largest number on top with the lowest number on the bottom
    • To post as a guest, your comment is unpublished.
      Xander · 3 years ago
      Change (Order1:=xlAscending, Header:=xlYes, ) To (Order1:=xlDescending, Header:=xlYes, )
  • To post as a guest, your comment is unpublished.
    SHABEERALI · 4 years ago
    Any one know how to set set an auto serial no. for an excel work sheet. The number should excludes all heading and sub headings, and should auto re arrange after adding or deleting a row.
    • To post as a guest, your comment is unpublished.
      kelly.extendoffice@gmail.com · 4 years ago
      Hi,
      Kutools for Excel provides an Insert Sequence Number feature, which can insert unique and consecutive values no matter how many times you insert.
      For example, the first time you insert 1,2,3,4;
      The second time this feature will insert 5,6,7,8,9,10
      The third time this feature will insert 11,12,…
  • To post as a guest, your comment is unpublished.
    Sushant · 4 years ago
    Thanks for code and logic. The above logic is not working when we are looking at bigger range of cells like Intersect(Target, Range("B:B,C:C,D:D")). I want if there is change in any cell in the Column (B,C or D), resorting needs to be done.
  • To post as a guest, your comment is unpublished.
    Tarcisio · 4 years ago
    THANK YOU!!!
  • To post as a guest, your comment is unpublished.
    Logan · 4 years ago
    Copied the code exactly into my sheet in VBA. But nothing is happening am I missing something? Did exactly what you did.
  • To post as a guest, your comment is unpublished.
    Rezal · 4 years ago
    First row is excluded for the sorting
    • To post as a guest, your comment is unpublished.
      redcode.researcher@gmail.com · 3 years ago
      I had the same problem and I found out why. Go to line 5, (Order1:=xlAscending, Header:=xlYes, _). The script is telling Excel the first cell is a header, and therefore it will ignore it when the sorting takes place. If you want to include the first row, change it to the following line:


      Order1:=xlAscending, Header:=xlNo, _
  • To post as a guest, your comment is unpublished.
    nikos · 4 years ago
    Hello. Excellent trick, thank you very much. In my file, I have important info which I need to see in cells A1 and A2. Therefore, I have stabilized all cells and my values start from A3 to A500. When I execute this vba, my info in cell A2 goes in cell 501. How can I make it start ranging form cell A3? Thank you in advance!
  • To post as a guest, your comment is unpublished.
    Brandon · 4 years ago
    Say I need to auto sort column "C" and column "D". How would I modify the code to sort both columns?
  • To post as a guest, your comment is unpublished.
    SK · 4 years ago
    This is GREAT. This was of great help.
  • To post as a guest, your comment is unpublished.
    Dean Marshall · 4 years ago
    I am looking for the same automatic sorting when entering data for my excel chart. Finding it hard to understand the details of what you have entered here as example. I would really appreciate it if someone here who did the example above could help me sort my sheet out so it did the same. I have a F1 chart that shows winners, and points, and would like it so that when the points are entered next to the drivers name for a specific race for the total on the right hand side of the sheet to be in numerical order, WITHOUT MIXING THE RESULT against other drivers. It seems that I can not place on this message an attachment of my chart so if you are able to alter my chart for me so this will happen I would like to hear from you. I can send it via email to you. If you can help with this then please email me at lordmarshall2013@gmail.com and in the SUBJECT line please write F1 Excel help and will reply with the chart attached for you. Thanks in advance for any help you can provide.
  • To post as a guest, your comment is unpublished.
    Alicia · 5 years ago
    Beautiful, beautiful, beautiful, thank you SO much. Would have never been able to figure this out on my own and I really appreciate you sharing this!!!