{"id":132,"date":"2012-02-23T21:53:47","date_gmt":"2012-02-23T20:53:47","guid":{"rendered":"http:\/\/mariaevert.dk\/vba\/?p=132"},"modified":"2013-02-23T22:50:49","modified_gmt":"2013-02-23T21:50:49","slug":"vba-retrieve-last-row-from-more-than-one-column","status":"publish","type":"post","link":"https:\/\/mariaevert.dk\/vba\/?p=132","title":{"rendered":"VBA &#8211; Retrieve Last Row From More Than One Column"},"content":{"rendered":"<p>Normally, it&#8217;s enough to know the last row in a specific column. In those cases I normally just use this widely method: <\/p>\n<pre class=\"brush: vb; title: ; notranslate\" title=\"\">\r\n'Getting last row from column A\r\nLastRow = Range(&quot;A&quot; &amp; Rows.Count).End(xlUp).row\r\n<\/pre>\n<p>But when you have several columns (for example 1 to 10) and you need to retrieve the last cell in use in any of these columns, we need something else. A big thanks to Jan Brun Rasmussen for having provided the following brilliant solution:  <\/p>\n<pre class=\"brush: vb; title: ; notranslate\" title=\"\">\r\n\r\nSub DefineRange()\r\n\r\nDim StartColumn As Integer\r\nDim EndColumn As Integer\r\nDim LastRow\r\n\r\n'define first and last column of the range\r\nStartColumn = 2\r\nEndColumn = 10\r\n\r\nLastRow = FindLastRow(StartColumn, EndColumn) 'call the function FindLastRow and return the value to the variable LastRow\r\nMsgBox &quot;Last row is &quot; &amp; LastRow 'show and tell\r\nEnd Sub\r\n\r\n'---the above sub calls the function below, providing the function with the range parameters. \r\n\r\nFunction FindLastRow(iColI As Integer, iColN As Integer) As Long\r\n    Dim iRowN As Long\r\n    Dim iRowI As Long\r\n    'Loop thorugh the columns\r\n    For i = iColI To iColN\r\n        'Define each columns' last row\r\n        iRowI = Cells(Rows.Count, i).End(xlUp).Row\r\n        'if last row is larger than in the previous column, save row number to iRowI\r\n        If iRowI &gt; iRowN Then iRowN = iRowI\r\n    Next i\r\n    FindLastRow = iRowN\r\nEnd Function\r\n\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Normally, it&#8217;s enough to know the last row in a specific column. In those cases I normally just use this widely method: But when you have several columns (for example 1 to 10) and you need to retrieve the last cell in use in any of these columns, we need something else. A big thanks [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/posts\/132"}],"collection":[{"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=132"}],"version-history":[{"count":14,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/posts\/132\/revisions"}],"predecessor-version":[{"id":184,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/posts\/132\/revisions\/184"}],"wp:attachment":[{"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=132"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=132"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=132"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}