{"id":12080,"date":"2011-11-06T15:43:46","date_gmt":"2011-11-06T15:43:46","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=299"},"modified":"2022-08-02T14:06:23","modified_gmt":"2022-08-02T14:06:23","slug":"sorting-using-macro","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/","title":{"rendered":"Sorting using Excel Macro"},"content":{"rendered":"

[et_pb_section fb_built=”1″ admin_label=”section” _builder_version=”4.17.6″ hover_enabled=”0″ global_colors_info=”{}” custom_padding=”0px|0px|0px|0px|false|false” sticky_enabled=”0″ da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on” da_disable_devices=”off|off|off”][et_pb_row admin_label=”row” _builder_version=”4.17.6″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” hover_enabled=”0″ global_colors_info=”{}” custom_padding=”0px|0px|0px|0px|false|false” sticky_enabled=”0″][et_pb_column type=”4_4″ _builder_version=”4.16″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text _builder_version=”4.17.6″ _module_preset=”default” hover_enabled=”0″ sticky_enabled=”0″ custom_padding=”0px|0px|0px|0px|false|false”]Sorting<\/strong> in excel sheet, is a very common functionality. Most excel users should be knowing already. But here we are going to learn how to do sorting using Excel VBA. If you are working on any automation in excel, it will be useful to know. You might want to provide users with a sorting button so that they can sort their data simply with a click. <\/p>\n

Types of sorting in Excel<\/h1>\n

There are 2 types of sorting available in Excel. They are Simple <\/a> and Custom<\/a><\/strong> Sorting. <\/p>\n

<\/a><\/p>\n

Simple Sorting<\/h1>\n

Simple sorting is nothing but sorting alphabetically or by number in ascending or descending order.<\/p>\n

\nSub Sorting()\n\n'---  First Select the Range which you want to sort\n\nRange(\"I8:L15\").Select\n\n'---  Now clear the Sort fields before sorting. This is important otherwise sorting will not take place\n\nActiveWorkbook.Worksheets(\"Sheet1\").Sort.SortFields.Clear\nActiveWorkbook.Worksheets(\"Sheet1\").Sort.SortFields.Add Key:=Range(\"I8\"), SortOn:=xlSortOnValues, _\nOrder:=xlAscending, DataOption:=xlSortNormal\nWith ActiveWorkbook.Worksheets(\"Sheet1\").Sort\n\n'---  Select the Range on which you want to perform the Sort.\n'---  If it includes the header row as well then put .Header=xlYes, else xlNo.\n\n .SetRange Range(\"I8:L15\")\n.Header = xlYes\n\n'---  It is better to put MatchCase as False. In case you want Sorting as Case senstavie then you can make it \"True\"\n\n.MatchCase = False\n.Orientation = xlTopToBottom\n.SortMethod = xlPinYin\n.Apply\nEnd With\nEnd Sub\n<\/code><\/pre>\n
 
\n<\/a><\/p>\n
 Custom Sorting <\/h1>\n
In custom sorting, we do the sorting on the basis of a list of Values. For example: sorting by Month\u2019s or Day\u2019s Name if we do the simple sorting then, all the Months will be sorted alphabetically and so the order will no longer be valid. For such sorting, we create a custom list in an Ascending or Descending Order, how you want.<\/p>\n
Note: CustomOrder can have any number of Values in any Order. Sorting will be done exactly in the same order.<\/p>\n
For Example:<\/h2>\n
    \n
    \n
  1. For Month Sorting: <\/li>\n<\/h4>\n
    <\/span>CustomOrder:= \"Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec\"<\/span><\/code><\/p>\n
    \n
  2.  For Some Custom Sorting: <\/li>\n<\/h4>\n
    <\/span>CustomOrder:= \"Passed,Failed,Inquiry,Defect\"<\/span><\/code>\n<\/ol>\n
    \nSub Sorting()\n\n'---  First Select the Range which you want to sort\u00a0\n\nRange(\"I8:L15\").Select\n\n'---  Now clear the Sort fields before sorting. This is important otherwise sorting will not take place\n\nActiveWorkbook.Worksheets(\"Sheet1\").Sort.SortFields.Clear\n\n'---  For custom Sorting we need to add the custom list in same order how you want to sort.\n\nActiveWorkbook.Worksheets(\"Sheet1\").Sort.SortFields.Add Key:=Range(\"I8\"), SortOn:=xlSortOnValues, _\nOrder:=xlAscending, CustomOrder:= \"Mon,Tue,Wed,Thu,Fri,Sat,Sun\", DataOption:=xlSortNormal\nWith ActiveWorkbook.Worksheets(\"Sheet1\").Sort\n\n'---  Select the Range on which you want to perform the Sort. \n'---  If it includes the header row as well then put .Header=xlYes, else xlNo \n\n.SetRange Range(\"I8:L15\")\n.Header = xlYes\n.MatchCase = False\n.Orientation = xlTopToBottom\n.SortMethod = xlPinYin\n.Apply\nEnd With\nEnd Sub\n<\/code><\/pre>\n
    [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n<\/span>","protected":false},"excerpt":{"rendered":"
    Sorting in excel sheet, is a very common functionality. Most excel users should be knowing already. But here we are going to learn how to do sorting using Excel VBA. If you are working on any automation in excel, it will be useful to know. You might want to provide users with a sorting button […]<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1246,1674],"tags":[],"class_list":["post-12080","post","type-post","status-publish","format-standard","hentry","category-macro","category-excel-macro-basics"],"yoast_head":"\nSorting using Excel Macros | Simple Sorting | Custom Sorting<\/title>\n<meta name=\"description\" content=\"Here we are going to learn how to do sorting using Excel VBA\/Macro. Basically there are two types of Sorting: Simple Sorting and Custom Sorting. Both types of Sorting is explained below with VBA Code\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Sorting using Excel Macro\" \/>\n<meta property=\"og:description\" content=\"Here we are going to learn how to do sorting using Excel VBA\/Macro. Basically there are two types of Sorting: Simple Sorting and Custom Sorting. Both types of Sorting is explained below with VBA Code\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2011-11-06T15:43:46+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-08-02T14:06:23+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/vmlogger.com_-1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\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:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"Sorting using Excel Macro\",\"datePublished\":\"2011-11-06T15:43:46+00:00\",\"dateModified\":\"2022-08-02T14:06:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/\"},\"wordCount\":349,\"commentCount\":3,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"articleSection\":[\"Excel Macro\",\"Excel Macro Basics\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/\",\"name\":\"Sorting using Excel Macros | Simple Sorting | Custom Sorting\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"datePublished\":\"2011-11-06T15:43:46+00:00\",\"dateModified\":\"2022-08-02T14:06:23+00:00\",\"description\":\"Here we are going to learn how to do sorting using Excel VBA\/Macro. Basically there are two types of Sorting: Simple Sorting and Custom Sorting. Both types of Sorting is explained below with VBA Code\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Macro\",\"item\":\"https:\/\/vmlogger.com\/excel\/macro\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Sorting using Excel Macro\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Sorting using Excel Macros | Simple Sorting | Custom Sorting","description":"Here we are going to learn how to do sorting using Excel VBA\/Macro. Basically there are two types of Sorting: Simple Sorting and Custom Sorting. Both types of Sorting is explained below with VBA Code","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:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/","og_locale":"en_US","og_type":"article","og_title":"Sorting using Excel Macro","og_description":"Here we are going to learn how to do sorting using Excel VBA\/Macro. Basically there are two types of Sorting: Simple Sorting and Custom Sorting. Both types of Sorting is explained below with VBA Code","og_url":"https:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2011-11-06T15:43:46+00:00","article_modified_time":"2022-08-02T14:06:23+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/vmlogger.com_-1.png","type":"image\/png"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"Sorting using Excel Macro","datePublished":"2011-11-06T15:43:46+00:00","dateModified":"2022-08-02T14:06:23+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/"},"wordCount":349,"commentCount":3,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"articleSection":["Excel Macro","Excel Macro Basics"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/","url":"https:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/","name":"Sorting using Excel Macros | Simple Sorting | Custom Sorting","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"datePublished":"2011-11-06T15:43:46+00:00","dateModified":"2022-08-02T14:06:23+00:00","description":"Here we are going to learn how to do sorting using Excel VBA\/Macro. Basically there are two types of Sorting: Simple Sorting and Custom Sorting. Both types of Sorting is explained below with VBA Code","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2011\/11\/sorting-using-macro\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Macro","item":"https:\/\/vmlogger.com\/excel\/macro\/"},{"@type":"ListItem","position":3,"name":"Sorting using Excel Macro"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12080"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=12080"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12080\/revisions"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=12080"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=12080"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=12080"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}