{"id":6196,"date":"2017-06-28T06:46:38","date_gmt":"2017-06-28T06:46:38","guid":{"rendered":"https:\/\/wordherd.io\/how-to-sort-an-excel-list-conditionally-using-vba-code\/"},"modified":"2017-06-28T06:46:38","modified_gmt":"2017-06-28T06:46:38","slug":"how-to-sort-an-excel-list-conditionally-using-vba-code","status":"publish","type":"post","link":"https:\/\/wordherd.io\/fr\/blog\/how-to-sort-an-excel-list-conditionally-using-vba-code\/","title":{"rendered":"How To Sort An Excel List Conditionally Using VBA Code"},"content":{"rendered":"<p>It is simple sufficient to kind a listing utilizing Excel&#8217;s normal sorting instruments or making use of a operate instantly in VBA code. However it&#8217;s slightly tougher to kind a listing the place you&#8217;ll want to apply your personal standards.<\/p>\n<p><strong>An Instance Of Conditional Sorting<\/strong><\/p>\n<p>A typical state of affairs is likely to be to kind alphabetically the next record of nations, however at all times have the large areas just like the USA, UK and Japan on the prime of the record.<\/p>\n<p><pre> \n<br>Nation\n<br>New Zealand\n<br>Australia\n<br>USA\n<br>Mexico\n<br>Belgium\n<br>UK\n<br>Japan\n<br><\/pre>\n<\/p>\n<p>We&#8217;ll create a brand new record utilizing some easy VBA code which you can adapt to fulfill your personal wants.<\/p>\n<p><strong>Organizing The Code<\/strong><\/p>\n<p>One resolution to this downside is to reorganize the record so the highest international locations are on the prime after which kind the 2 areas of the record individually.<\/p>\n<p>First, we&#8217;ll outline the names and variety of international locations we need to seem on the prime of the record.<\/p>\n<p><pre>\n<br>topItems = \",USA,UK,Japan,\"\n<br>ctItems = UBound(Cut up(topItems, \",\")) - 1\n<br><\/pre>\n<\/p>\n<p>Subsequent, we will choose the record and set a counter for the variety of &#8220;prime&#8221; international locations and &#8220;others&#8221;.<\/p>\n<p><pre>\n<br>Set rng = ActiveCell.CurrentRegion\n<br>prime = 1\n<br>others = 1\n<br><\/pre>\n<\/p>\n<p>Now we&#8217;re able to separate out the record into the highest international locations and others which we&#8217;ll do by transferring every nation into a brand new record alongside the outdated one. Remember we have to ignore the header row.<\/p>\n<p><pre>\n<br>For x = 2 To rng.Rows.Rely\n<br><\/pre>\n<\/p>\n<p>If the present cell worth is among the prime international locations then we&#8217;ll transfer the worth to the highest of a brand new record, and if not we&#8217;ll transfer it to the underside of the brand new record.<\/p>\n<p><pre>\n<br>If InStr(topItems, \",\" & rng.Rows(x) & \",\") Then\n<br>prime = prime + 1\n<br>Cells(prime, 2) = rng.Rows(x)\n<br>Else\n<br>others = others + 1\n<br>Cells(others + ctItems, 2) = rng.Rows(x)\n<br>Finish If\n<br>Subsequent\n<br><\/pre>\n<\/p>\n<p>Our record is now reorganized within the following manner, and we simply have to kind the underside a part of the record in column 2.<\/p>\n<p><pre>\n<br>USA\n<br>UK\n<br>Japan\n<br>New Zealand\n<br>Australia\n<br>Mexico\n<br>Belgium\n<br><\/pre>\n<\/p>\n<p>The next code types the record beneath the highest international locations in column 2. As a result of we all know what number of prime international locations there are, the vary begins two rows beneath that worth &#8211; to take into consideration the header row.<\/p>\n<p><pre>\n<br>Set rng = Vary(\"b\" & ctItems + 2 & \":\" & ActiveCell.Finish(xlDown).Deal with)\n<br>rng.Kind Key1:=Vary(\"b1\"), order1:=xlAscending\n<br><\/pre>\n<\/p>\n<p>The code produces a closing outcome wanting like this:<\/p>\n<p><pre>\n<br>USA\n<br>UK\n<br>Japan\n<br>Australia\n<br>Belgium\n<br>Mexico\n<br>New Zealand\n<br><\/pre>\n<\/p>\n<p>One space for growth is likely to be to rearrange the highest international locations in a sure order. It might be straightforward sufficient to exhausting code an answer, however it&#8217;s good follow to have a scalable resolution; for instance it is likely to be a listing of consumers and you&#8217;ll want to spotlight your prime 100 purchasers.<\/p>\n<p><strong>Abstract<\/strong><br \/>\n<br \/>This brief VBA code offers an answer to an issue not readily solvable through the use of the usual Excel instruments. It is the kind of state of affairs VBA builders usually face and a very good candidate for saving in a useful location for future reference.<\/p>\n<div class=\"source\"><a href=\"http:\/\/ezinearticles.com\/?How-To-Sort-An-Excel-List-Conditionally-Using-VBA-Code&#038;id=7362888\">Source<\/a> par <a href=\"http:\/\/ezinearticles.com\/expert\/Andy_L_Gibson\/1382722\">Andy L Gibson<\/a><\/div>","protected":false},"excerpt":{"rendered":"<p>It is simple sufficient to kind a listing utilizing Excel&#8217;s normal sorting instruments or making use of a operate instantly in VBA code. However it&#8217;s slightly tougher to kind a listing the place you&#8217;ll want to apply your personal standards. An Instance Of Conditional Sorting A typical state of affairs is likely to be to [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"content-type":"","inline_featured_image":false,"footnotes":""},"categories":[15],"tags":[200,595],"builders":[],"industry":[],"platform":[],"class_list":["post-6196","post","type-post","status-publish","format-standard","hentry","category-wordpress","tag-wordpress","tag-wordpress-tutorial"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v28.8 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>How To Sort An Excel List Conditionally Using VBA Code - WordHerd WordPress Migration<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/wordherd.io\/fr\/blog\/how-to-sort-an-excel-list-conditionally-using-vba-code\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How To Sort An Excel List Conditionally Using VBA Code - WordHerd WordPress Migration\" \/>\n<meta property=\"og:description\" content=\"It is simple sufficient to kind a listing utilizing Excel&#8217;s normal sorting instruments or making use of a operate instantly in VBA code. However it&#8217;s slightly tougher to kind a listing the place you&#8217;ll want to apply your personal standards. An Instance Of Conditional Sorting A typical state of affairs is likely to be to [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/wordherd.io\/fr\/blog\/how-to-sort-an-excel-list-conditionally-using-vba-code\/\" \/>\n<meta property=\"og:site_name\" content=\"WordHerd WordPress Migration\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/wordherdio\/\" \/>\n<meta property=\"article:published_time\" content=\"2017-06-28T06:46:38+00:00\" \/>\n<meta name=\"author\" content=\"WordHerd\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@wordherdio\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"WordHerd\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/wordherd.io\\\/blog\\\/how-to-sort-an-excel-list-conditionally-using-vba-code\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/wordherd.io\\\/blog\\\/how-to-sort-an-excel-list-conditionally-using-vba-code\\\/\"},\"author\":{\"name\":\"WordHerd\",\"@id\":\"https:\\\/\\\/wordherd.io\\\/#\\\/schema\\\/person\\\/373419b97c0fbc45a3a16462bdba0fac\"},\"headline\":\"How To Sort An Excel List Conditionally Using VBA Code\",\"datePublished\":\"2017-06-28T06:46:38+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/wordherd.io\\\/blog\\\/how-to-sort-an-excel-list-conditionally-using-vba-code\\\/\"},\"wordCount\":457,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/wordherd.io\\\/#organization\"},\"keywords\":[\"WordPress\",\"wordpress tutorial\"],\"articleSection\":[\"WordPress\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/wordherd.io\\\/blog\\\/how-to-sort-an-excel-list-conditionally-using-vba-code\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/wordherd.io\\\/blog\\\/how-to-sort-an-excel-list-conditionally-using-vba-code\\\/\",\"url\":\"https:\\\/\\\/wordherd.io\\\/blog\\\/how-to-sort-an-excel-list-conditionally-using-vba-code\\\/\",\"name\":\"How To Sort An Excel List Conditionally Using VBA Code - WordHerd WordPress Migration\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/wordherd.io\\\/#website\"},\"datePublished\":\"2017-06-28T06:46:38+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/wordherd.io\\\/blog\\\/how-to-sort-an-excel-list-conditionally-using-vba-code\\\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/wordherd.io\\\/blog\\\/how-to-sort-an-excel-list-conditionally-using-vba-code\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/wordherd.io\\\/blog\\\/how-to-sort-an-excel-list-conditionally-using-vba-code\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/wordherd.io\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How To Sort An Excel List Conditionally Using VBA Code\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/wordherd.io\\\/#website\",\"url\":\"https:\\\/\\\/wordherd.io\\\/\",\"name\":\"WordHerd WordPress Migration\",\"description\":\"Website Migration Services\",\"publisher\":{\"@id\":\"https:\\\/\\\/wordherd.io\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/wordherd.io\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/wordherd.io\\\/#organization\",\"name\":\"WordHerd WordPress Migration\",\"url\":\"https:\\\/\\\/wordherd.io\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\\\/\\\/wordherd.io\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/wordherd.io\\\/wp-content\\\/uploads\\\/wordherd-trademark.png\",\"contentUrl\":\"https:\\\/\\\/wordherd.io\\\/wp-content\\\/uploads\\\/wordherd-trademark.png\",\"width\":1000,\"height\":315,\"caption\":\"WordHerd WordPress Migration\"},\"image\":{\"@id\":\"https:\\\/\\\/wordherd.io\\\/#\\\/schema\\\/logo\\\/image\\\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/wordherd.io\\\/#\\\/schema\\\/person\\\/373419b97c0fbc45a3a16462bdba0fac\",\"name\":\"WordHerd\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/bf8c7166601a64c6c57b67ffbeab8f8c6a378cca786da9a326c1a34a2ec6f860?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/bf8c7166601a64c6c57b67ffbeab8f8c6a378cca786da9a326c1a34a2ec6f860?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/bf8c7166601a64c6c57b67ffbeab8f8c6a378cca786da9a326c1a34a2ec6f860?s=96&d=mm&r=g\",\"caption\":\"WordHerd\"},\"sameAs\":[\"https:\\\/\\\/wordherd.io\",\"https:\\\/\\\/www.facebook.com\\\/wordherdio\\\/\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/wordherd\",\"https:\\\/\\\/x.com\\\/wordherdio\"],\"url\":\"https:\\\/\\\/wordherd.io\\\/fr\\\/blog\\\/author\\\/wordherd\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How To Sort An Excel List Conditionally Using VBA Code - WordHerd WordPress Migration","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/wordherd.io\/fr\/blog\/how-to-sort-an-excel-list-conditionally-using-vba-code\/","og_locale":"fr_FR","og_type":"article","og_title":"How To Sort An Excel List Conditionally Using VBA Code - WordHerd WordPress Migration","og_description":"It is simple sufficient to kind a listing utilizing Excel&#8217;s normal sorting instruments or making use of a operate instantly in VBA code. However it&#8217;s slightly tougher to kind a listing the place you&#8217;ll want to apply your personal standards. An Instance Of Conditional Sorting A typical state of affairs is likely to be to [&hellip;]","og_url":"https:\/\/wordherd.io\/fr\/blog\/how-to-sort-an-excel-list-conditionally-using-vba-code\/","og_site_name":"WordHerd WordPress Migration","article_author":"https:\/\/www.facebook.com\/wordherdio\/","article_published_time":"2017-06-28T06:46:38+00:00","author":"WordHerd","twitter_card":"summary_large_image","twitter_creator":"@wordherdio","twitter_misc":{"\u00c9crit par":"WordHerd","Dur\u00e9e de lecture estim\u00e9e":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/wordherd.io\/blog\/how-to-sort-an-excel-list-conditionally-using-vba-code\/#article","isPartOf":{"@id":"https:\/\/wordherd.io\/blog\/how-to-sort-an-excel-list-conditionally-using-vba-code\/"},"author":{"name":"WordHerd","@id":"https:\/\/wordherd.io\/#\/schema\/person\/373419b97c0fbc45a3a16462bdba0fac"},"headline":"How To Sort An Excel List Conditionally Using VBA Code","datePublished":"2017-06-28T06:46:38+00:00","mainEntityOfPage":{"@id":"https:\/\/wordherd.io\/blog\/how-to-sort-an-excel-list-conditionally-using-vba-code\/"},"wordCount":457,"commentCount":0,"publisher":{"@id":"https:\/\/wordherd.io\/#organization"},"keywords":["WordPress","wordpress tutorial"],"articleSection":["WordPress"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/wordherd.io\/blog\/how-to-sort-an-excel-list-conditionally-using-vba-code\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/wordherd.io\/blog\/how-to-sort-an-excel-list-conditionally-using-vba-code\/","url":"https:\/\/wordherd.io\/blog\/how-to-sort-an-excel-list-conditionally-using-vba-code\/","name":"How To Sort An Excel List Conditionally Using VBA Code - WordHerd WordPress Migration","isPartOf":{"@id":"https:\/\/wordherd.io\/#website"},"datePublished":"2017-06-28T06:46:38+00:00","breadcrumb":{"@id":"https:\/\/wordherd.io\/blog\/how-to-sort-an-excel-list-conditionally-using-vba-code\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/wordherd.io\/blog\/how-to-sort-an-excel-list-conditionally-using-vba-code\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/wordherd.io\/blog\/how-to-sort-an-excel-list-conditionally-using-vba-code\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/wordherd.io\/"},{"@type":"ListItem","position":2,"name":"How To Sort An Excel List Conditionally Using VBA Code"}]},{"@type":"WebSite","@id":"https:\/\/wordherd.io\/#website","url":"https:\/\/wordherd.io\/","name":"Migration de WordHerd vers WordPress","description":"Services de migration de sites web","publisher":{"@id":"https:\/\/wordherd.io\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/wordherd.io\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"fr-FR"},{"@type":"Organization","@id":"https:\/\/wordherd.io\/#organization","name":"Migration de WordHerd vers WordPress","url":"https:\/\/wordherd.io\/","logo":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/wordherd.io\/#\/schema\/logo\/image\/","url":"https:\/\/wordherd.io\/wp-content\/uploads\/wordherd-trademark.png","contentUrl":"https:\/\/wordherd.io\/wp-content\/uploads\/wordherd-trademark.png","width":1000,"height":315,"caption":"WordHerd WordPress Migration"},"image":{"@id":"https:\/\/wordherd.io\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/wordherd.io\/#\/schema\/person\/373419b97c0fbc45a3a16462bdba0fac","name":"WordHerd","image":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/secure.gravatar.com\/avatar\/bf8c7166601a64c6c57b67ffbeab8f8c6a378cca786da9a326c1a34a2ec6f860?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/bf8c7166601a64c6c57b67ffbeab8f8c6a378cca786da9a326c1a34a2ec6f860?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/bf8c7166601a64c6c57b67ffbeab8f8c6a378cca786da9a326c1a34a2ec6f860?s=96&d=mm&r=g","caption":"WordHerd"},"sameAs":["https:\/\/wordherd.io","https:\/\/www.facebook.com\/wordherdio\/","https:\/\/www.linkedin.com\/company\/wordherd","https:\/\/x.com\/wordherdio"],"url":"https:\/\/wordherd.io\/fr\/blog\/author\/wordherd\/"}]}},"_links":{"self":[{"href":"https:\/\/wordherd.io\/fr\/wp-json\/wp\/v2\/posts\/6196","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wordherd.io\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wordherd.io\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wordherd.io\/fr\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/wordherd.io\/fr\/wp-json\/wp\/v2\/comments?post=6196"}],"version-history":[{"count":0,"href":"https:\/\/wordherd.io\/fr\/wp-json\/wp\/v2\/posts\/6196\/revisions"}],"wp:attachment":[{"href":"https:\/\/wordherd.io\/fr\/wp-json\/wp\/v2\/media?parent=6196"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wordherd.io\/fr\/wp-json\/wp\/v2\/categories?post=6196"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wordherd.io\/fr\/wp-json\/wp\/v2\/tags?post=6196"},{"taxonomy":"builders","embeddable":true,"href":"https:\/\/wordherd.io\/fr\/wp-json\/wp\/v2\/builders?post=6196"},{"taxonomy":"industry","embeddable":true,"href":"https:\/\/wordherd.io\/fr\/wp-json\/wp\/v2\/industry?post=6196"},{"taxonomy":"platform","embeddable":true,"href":"https:\/\/wordherd.io\/fr\/wp-json\/wp\/v2\/platform?post=6196"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}