{"id":5985,"date":"2018-03-18T09:32:25","date_gmt":"2018-03-18T09:32:25","guid":{"rendered":"http:\/\/putridparrot.com\/blog\/?p=5985"},"modified":"2018-03-18T09:32:25","modified_gmt":"2018-03-18T09:32:25","slug":"sorting-a-range-on-specific-column-of-data-using-vba-in-excel","status":"publish","type":"post","link":"https:\/\/putridparrot.com\/blog\/sorting-a-range-on-specific-column-of-data-using-vba-in-excel\/","title":{"rendered":"Sorting a range on specific column of data using VBA in Excel"},"content":{"rendered":"<p>In my previous post I imported a CSV file into my spreadsheet and displayed the rows after my headings row. I now want to sort the rows by a column of data, but I do not want the heading to be part of the sort, so I basically want to sort the rows based upon the column starting at the row below the column headings &#8211; I hope that makes sense.<\/p>\n<p>Anyway, we&#8217;re going to need to find all the rows and columns that make our range of data and then select a slice of the data which are our value items to sort. Here&#8217;s the code<\/p>\n<pre class=\"brush: vb; title: ; notranslate\" title=\"\">\r\nrows = ws.Cells(Rows.Count, &quot;A&quot;).End(xlUp).Row\r\ncolumns = ws.Cells(3, Columns.Count).End(xlToLeft).Column\r\n    \r\nSet dataRange = ws.Range(Cells(3, 1), Cells(rows, columns))\r\nSet sortColumn = ws.Range(Cells(3, 5), Cells(rows, 5))\r\ndataRange.Sort key1:=sortColumn, order1:=xlDescending\r\n<\/pre>\n<p>In this example we set <em>rows<\/em> to be the row count and <em>columns<\/em> to be the number of columns starting at row 3 (where our data is imported to). Next we turn these values into a range object (mine&#8217;s named <em>dataRange<\/em>) again using row 3 as our data&#8217;s starting point. Next we create a <em>sortColumn<\/em> range to be the actual column we want to use as our sort key, in this case I&#8217;m using column 5 data. Finally we sort the first data range using the second and mine sort is descending in this example.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In my previous post I imported a CSV file into my spreadsheet and displayed the rows after my headings row. I now want to sort the rows by a column of data, but I do not want the heading to be part of the sort, so I basically want to sort the rows based upon [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[54],"tags":[],"class_list":["post-5985","post","type-post","status-publish","format-standard","hentry","category-excel"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/5985","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/comments?post=5985"}],"version-history":[{"count":3,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/5985\/revisions"}],"predecessor-version":[{"id":6006,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/5985\/revisions\/6006"}],"wp:attachment":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/media?parent=5985"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/categories?post=5985"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/tags?post=5985"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}