{"id":751,"date":"2019-09-26T19:44:25","date_gmt":"2019-09-26T19:44:25","guid":{"rendered":"http:\/\/www.vbastring.com\/blog\/?p=751"},"modified":"2019-09-26T19:44:58","modified_gmt":"2019-09-26T19:44:58","slug":"excel-vba-subtotal-on-a-dynamic-range","status":"publish","type":"post","link":"https:\/\/vbastring.com\/blog\/2019\/09\/26\/excel-vba-subtotal-on-a-dynamic-range\/","title":{"rendered":"Excel VBA Subtotal On A Dynamic Range"},"content":{"rendered":"<p>Hi Reader,<\/p>\n<p>This post will show you how to add subtotals on a variable group by column and total column, and it will also show you how to add some color to the subtotal rows to make them stand out.<\/p>\n<p>Here is what the data to which we want to add the subtotals looks like:<\/p>\n<p><a href=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/09\/VBA_SUBTOTAL_BEFORE.png\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/09\/VBA_SUBTOTAL_BEFORE.png?resize=684%2C643\" alt=\"\" width=\"684\" height=\"643\" class=\"aligncenter size-full wp-image-753\" \/><\/a><\/p>\n<p>and here is what it will look like after the code is run&#8230;<\/p>\n<p><a href=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/09\/VBA_SUBTOTAL_AFTER.png\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/09\/VBA_SUBTOTAL_AFTER-1024x692.png?resize=625%2C422\" alt=\"\" width=\"625\" height=\"422\" class=\"aligncenter size-large wp-image-754\" \/><\/a><\/p>\n<p>Here is the code:<\/p>\n<pre class=\"lang:vb decode:true \" >Sub PlaceSubtotals()\r\n\r\n    Dim strCurrentSheet As String\r\n    Dim intLastRow As Integer\r\n    Dim intGroupByCol As Integer\r\n    Dim intTotalCol As Integer\r\n    \r\n    Dim intRow As Integer\r\n    \r\n    strCurrentSheet = ActiveSheet.Name\r\n    intLastRow = FindLastRow(\"A\")\r\n    \r\n    '***************************************************************\r\n    'which column do we want to group by and enter the subtotals?\r\n    '***************************************************************\r\n    intGroupByCol = 3\r\n    intTotalCol = 4\r\n       \r\n    '*********************************************\r\n    'sort the group by column\r\n    '*********************************************\r\n    Range(ActiveSheet.Cells(1, intGroupByCol).Address).Select\r\n    ActiveWorkbook.Worksheets(strCurrentSheet).Sort.SortFields.Clear\r\n    \r\n    ActiveWorkbook.Worksheets(strCurrentSheet).Sort.SortFields.Add2 Key:=Range( _\r\n        Cells(2, intGroupByCol).Address), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _\r\n        xlSortNormal\r\n            \r\n    '*********************************************\r\n    'implement the subtotals\r\n    '*********************************************\r\n    With ActiveWorkbook.Worksheets(strCurrentSheet).Sort\r\n        .SetRange Range(\"A2:F\" &amp; intLastRow)\r\n        .Header = xlNo\r\n        .MatchCase = False\r\n        .Orientation = xlTopToBottom\r\n        .SortMethod = xlPinYin\r\n        .Apply\r\n    End With\r\n    \r\n    '*********************************************\r\n    'at each change in the GroupBy field add a total in the TotalList field\r\n    '*********************************************\r\n    Selection.Subtotal GroupBy:=intGroupByCol, Function:=xlSum, TotalList:=intTotalCol, _\r\n        Replace:=True, PageBreaks:=False, SummaryBelowData:=True\r\n        \r\n    '*********************************************\r\n    'finally add color to the subtotal cells\r\n    '*********************************************\r\n    intLastRow = FindLastRow2(intGroupByCol)\r\n    For intRow = 2 To intLastRow\r\n        If InStr(1, Cells(intRow, intGroupByCol), \"Total\") Then\r\n\r\n            Range(ActiveSheet.Cells(intRow, intGroupByCol).Address).Select\r\n            With Selection.Interior\r\n                .Pattern = xlSolid\r\n                .PatternColorIndex = xlAutomatic\r\n                .Color = 65535\r\n                .TintAndShade = 0\r\n                .PatternTintAndShade = 0\r\n            End With\r\n            \r\n        End If\r\n    Next\r\n    \r\nEnd Sub\r\n\r\nFunction FindLastRow(WhichColumn As String) As Long\r\n    \r\n    'FINDS THE LAST ROW BASED ON THE COLUMN LETTER &lt;---\r\n    \r\n    Dim lngLastRow As Long\r\n    \r\n    'move to the last row on the worksheet and find the last used cell.\r\n    \r\n    With ActiveSheet\r\n        lngLastRow = ActiveSheet.Cells(1048576, WhichColumn).End(xlUp).Row\r\n    End With\r\n\r\n    FindLastRow = lngLastRow\r\n\r\nEnd Function\r\n\r\nFunction FindLastRow2(WhichColumn As Integer) As Long\r\n\r\n    'FINDS THE LAST ROW BASED ON THE COLUMN NUMBER &lt;---\r\n    \r\n    Dim lngLastRow As Long\r\n    \r\n    'move to the last row on the worksheet and find the last used cell.\r\n    \r\n    With ActiveSheet\r\n        lngLastRow = ActiveSheet.Cells(1048576, WhichColumn).End(xlUp).Row\r\n    End With\r\n\r\n    FindLastRow2 = lngLastRow\r\n\r\nEnd Function\r\n<\/pre>\n<p>Let me know if you have any questions.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hi Reader, This post will show you how to add subtotals on a variable group by column and total column, and it will also show you how to add some color to the subtotal rows to make them stand out. Here is what the data to which we want to add the subtotals looks like: [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"nf_dc_page":"","om_disable_all_campaigns":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[],"tags":[],"class_list":["post-751","post","type-post","status-publish","format-standard","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Excel VBA Subtotal On A Dynamic Range - My Blog<\/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:\/\/vbastring.com\/blog\/2019\/09\/26\/excel-vba-subtotal-on-a-dynamic-range\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel VBA Subtotal On A Dynamic Range - My Blog\" \/>\n<meta property=\"og:description\" content=\"Hi Reader, This post will show you how to add subtotals on a variable group by column and total column, and it will also show you how to add some color to the subtotal rows to make them stand out. Here is what the data to which we want to add the subtotals looks like: [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vbastring.com\/blog\/2019\/09\/26\/excel-vba-subtotal-on-a-dynamic-range\/\" \/>\n<meta property=\"og:site_name\" content=\"My Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-09-26T19:44:25+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-09-26T19:44:58+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/09\/VBA_SUBTOTAL_BEFORE.png\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/09\\\/26\\\/excel-vba-subtotal-on-a-dynamic-range\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/09\\\/26\\\/excel-vba-subtotal-on-a-dynamic-range\\\/\"},\"author\":{\"name\":\"admin\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#\\\/schema\\\/person\\\/e1de0b30e98940381697872449c341f5\"},\"headline\":\"Excel VBA Subtotal On A Dynamic Range\",\"datePublished\":\"2019-09-26T19:44:25+00:00\",\"dateModified\":\"2019-09-26T19:44:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/09\\\/26\\\/excel-vba-subtotal-on-a-dynamic-range\\\/\"},\"wordCount\":87,\"image\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/09\\\/26\\\/excel-vba-subtotal-on-a-dynamic-range\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/www.vbastring.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/VBA_SUBTOTAL_BEFORE.png\",\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/09\\\/26\\\/excel-vba-subtotal-on-a-dynamic-range\\\/\",\"url\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/09\\\/26\\\/excel-vba-subtotal-on-a-dynamic-range\\\/\",\"name\":\"Excel VBA Subtotal On A Dynamic Range - My Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/09\\\/26\\\/excel-vba-subtotal-on-a-dynamic-range\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/09\\\/26\\\/excel-vba-subtotal-on-a-dynamic-range\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/www.vbastring.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/VBA_SUBTOTAL_BEFORE.png\",\"datePublished\":\"2019-09-26T19:44:25+00:00\",\"dateModified\":\"2019-09-26T19:44:58+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#\\\/schema\\\/person\\\/e1de0b30e98940381697872449c341f5\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/09\\\/26\\\/excel-vba-subtotal-on-a-dynamic-range\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/09\\\/26\\\/excel-vba-subtotal-on-a-dynamic-range\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/09\\\/26\\\/excel-vba-subtotal-on-a-dynamic-range\\\/#primaryimage\",\"url\":\"http:\\\/\\\/www.vbastring.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/VBA_SUBTOTAL_BEFORE.png\",\"contentUrl\":\"http:\\\/\\\/www.vbastring.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/VBA_SUBTOTAL_BEFORE.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/09\\\/26\\\/excel-vba-subtotal-on-a-dynamic-range\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel VBA Subtotal On A Dynamic Range\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/\",\"name\":\"My Blog\",\"description\":\"My WordPress Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#\\\/schema\\\/person\\\/e1de0b30e98940381697872449c341f5\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g\",\"caption\":\"admin\"},\"sameAs\":[\"https:\\\/\\\/vbastring.com\\\/blog\"],\"url\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/author\\\/admin\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Excel VBA Subtotal On A Dynamic Range - My Blog","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:\/\/vbastring.com\/blog\/2019\/09\/26\/excel-vba-subtotal-on-a-dynamic-range\/","og_locale":"en_US","og_type":"article","og_title":"Excel VBA Subtotal On A Dynamic Range - My Blog","og_description":"Hi Reader, This post will show you how to add subtotals on a variable group by column and total column, and it will also show you how to add some color to the subtotal rows to make them stand out. Here is what the data to which we want to add the subtotals looks like: [&hellip;]","og_url":"https:\/\/vbastring.com\/blog\/2019\/09\/26\/excel-vba-subtotal-on-a-dynamic-range\/","og_site_name":"My Blog","article_published_time":"2019-09-26T19:44:25+00:00","article_modified_time":"2019-09-26T19:44:58+00:00","og_image":[{"url":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/09\/VBA_SUBTOTAL_BEFORE.png","type":"","width":"","height":""}],"author":"admin","twitter_card":"summary_large_image","twitter_misc":{"Written by":"admin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vbastring.com\/blog\/2019\/09\/26\/excel-vba-subtotal-on-a-dynamic-range\/#article","isPartOf":{"@id":"https:\/\/vbastring.com\/blog\/2019\/09\/26\/excel-vba-subtotal-on-a-dynamic-range\/"},"author":{"name":"admin","@id":"https:\/\/vbastring.com\/blog\/#\/schema\/person\/e1de0b30e98940381697872449c341f5"},"headline":"Excel VBA Subtotal On A Dynamic Range","datePublished":"2019-09-26T19:44:25+00:00","dateModified":"2019-09-26T19:44:58+00:00","mainEntityOfPage":{"@id":"https:\/\/vbastring.com\/blog\/2019\/09\/26\/excel-vba-subtotal-on-a-dynamic-range\/"},"wordCount":87,"image":{"@id":"https:\/\/vbastring.com\/blog\/2019\/09\/26\/excel-vba-subtotal-on-a-dynamic-range\/#primaryimage"},"thumbnailUrl":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/09\/VBA_SUBTOTAL_BEFORE.png","inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/vbastring.com\/blog\/2019\/09\/26\/excel-vba-subtotal-on-a-dynamic-range\/","url":"https:\/\/vbastring.com\/blog\/2019\/09\/26\/excel-vba-subtotal-on-a-dynamic-range\/","name":"Excel VBA Subtotal On A Dynamic Range - My Blog","isPartOf":{"@id":"https:\/\/vbastring.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/vbastring.com\/blog\/2019\/09\/26\/excel-vba-subtotal-on-a-dynamic-range\/#primaryimage"},"image":{"@id":"https:\/\/vbastring.com\/blog\/2019\/09\/26\/excel-vba-subtotal-on-a-dynamic-range\/#primaryimage"},"thumbnailUrl":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/09\/VBA_SUBTOTAL_BEFORE.png","datePublished":"2019-09-26T19:44:25+00:00","dateModified":"2019-09-26T19:44:58+00:00","author":{"@id":"https:\/\/vbastring.com\/blog\/#\/schema\/person\/e1de0b30e98940381697872449c341f5"},"breadcrumb":{"@id":"https:\/\/vbastring.com\/blog\/2019\/09\/26\/excel-vba-subtotal-on-a-dynamic-range\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vbastring.com\/blog\/2019\/09\/26\/excel-vba-subtotal-on-a-dynamic-range\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vbastring.com\/blog\/2019\/09\/26\/excel-vba-subtotal-on-a-dynamic-range\/#primaryimage","url":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/09\/VBA_SUBTOTAL_BEFORE.png","contentUrl":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/09\/VBA_SUBTOTAL_BEFORE.png"},{"@type":"BreadcrumbList","@id":"https:\/\/vbastring.com\/blog\/2019\/09\/26\/excel-vba-subtotal-on-a-dynamic-range\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vbastring.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Excel VBA Subtotal On A Dynamic Range"}]},{"@type":"WebSite","@id":"https:\/\/vbastring.com\/blog\/#website","url":"https:\/\/vbastring.com\/blog\/","name":"My Blog","description":"My WordPress Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vbastring.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/vbastring.com\/blog\/#\/schema\/person\/e1de0b30e98940381697872449c341f5","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g","caption":"admin"},"sameAs":["https:\/\/vbastring.com\/blog"],"url":"https:\/\/vbastring.com\/blog\/author\/admin\/"}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/posts\/751","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/comments?post=751"}],"version-history":[{"count":3,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/posts\/751\/revisions"}],"predecessor-version":[{"id":760,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/posts\/751\/revisions\/760"}],"wp:attachment":[{"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/media?parent=751"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/categories?post=751"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/tags?post=751"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}