{"id":450,"date":"2019-02-06T22:36:26","date_gmt":"2019-02-06T22:36:26","guid":{"rendered":"http:\/\/www.vbastring.com\/blog\/?p=450"},"modified":"2019-02-17T21:01:22","modified_gmt":"2019-02-17T21:01:22","slug":"how-to-find-the-last-row-in-excel-vba","status":"publish","type":"post","link":"https:\/\/vbastring.com\/blog\/2019\/02\/06\/how-to-find-the-last-row-in-excel-vba\/","title":{"rendered":"How To Find The Last Row In Excel VBA"},"content":{"rendered":"

In this post I am going to show you how you can find the last row in excel vba, so you can enter your new data.<\/p>\n

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

On the previous screen I have a list of random names I generated from https:\/\/www.generatedata.com\/<\/a><\/p>\n

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

When the button “View\/Edit Records” is clicked, we’re launching a UserForm which allows us to move back and forth through the records, edit, and add new ones.<\/strong><\/p>\n

This is what happens when the UserForm is clicked:<\/p>\n

Private Sub UserForm_Initialize()\r\n    Range(\"A2\").Select\r\n    Me.txtName = Range(\"A2\")\r\nEnd Sub\r\n<\/pre>\n
Cell A2 is selected and the textbox shows the value of Cell A2.<\/p>\n
These are the buttons which control record navigation:<\/p>\n
\"\"<\/a><\/p>\n
\r\n...and here is the code:\r\n<\/pre>\n
'We will make a modular scope variable because we need this _\r\n    row reference as long as the form is open\r\n\r\nDim m_lngRow As Long\r\n\r\nPrivate Sub btnForward_Click()\r\n    \r\n    'increment the active row and sync the UserForm and the Activesheet\r\n    m_lngRow = ActiveCell.Row + 1\r\n    \r\n    'select the next cell and render the new data on the UserForm\r\n    Range(\"A\" & m_lngRow).Select\r\n    Me.txtName = Range(\"A\" & m_lngRow)\r\nEnd Sub\r\n\r\n\r\nPrivate Sub btnBack_Click()\r\n    \r\n    'increment the active row and sync the UserForm and the Activesheet\r\n    m_lngRow = ActiveCell.Row - 1\r\n    \r\n    'select the next cell and render the new data on the UserForm\r\n    Range(\"A\" & m_lngRow).Select\r\n    Me.txtName = Range(\"A\" & m_lngRow)\r\n    \r\nEnd Sub<\/pre>\n
Again, we have a modular scope variable because we need to know the row being referenced as long as the form is open, and if we just form it in the procedure, the scope will only be when one of the buttons are clicked.<\/p>\n
\"\"<\/a><\/p>\n
The “New” button is really the jist of this post.  <\/strong><\/p>\n
The plan is:
\n**First find the last cell used, then find the row of the cell.<\/p>\n
***We could move from the “top down”, or the “bottom up”.<\/strong><\/p>\n
With the “top down” approach, we’ll find the next available cell, but the blank cell may be between 2 already used cells. <\/strong> <\/p>\n
In my opinion, we can move to the last row on the worksheet, and then upwards to find the last used cell.  <\/strong><\/p>\n
This is the better option IMO.<\/p>\n
Private Sub btnNew_Click()\r\n    Dim lngLastRow As Long\r\n    \r\n    'Find the last row used\r\n    lngLastRow = FindLastRow(\"A\")\r\n    \r\n    lngLastRow = lngLastRow + 1\r\n    Range(\"A\" & lngLastRow).Select\r\n    Me.txtName = Range(\"A\" & lngLastRow)\r\nEnd Sub\r\n\r\nFunction FindLastRow(WhichColumn As String) As Long\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<\/pre>\n
Now that we found the last row, we can enter new data:<\/p>\n
\"\"<\/a><\/p>\n
Private Sub btnEnter_Click()\r\n    Range(\"A\" & ActiveCell.Row) = Me.txtName\r\n    \r\nEnd Sub\r\n<\/pre>\n
This is pretty simple, we are just setting the cell value = to the form value.<\/p>\n
Here is all the code:<\/strong><\/p>\n
\r\n\r\n'We will make a modular scope variable because we need this _\r\n    row reference as long as the form is open\r\n\r\nDim m_lngRow As Long\r\n\r\nPrivate Sub btnForward_Click()\r\n    \r\n    'increment the active row and sync the UserForm and the Activesheet\r\n    m_lngRow = ActiveCell.Row + 1\r\n    \r\n    'select the next cell and render the new data on the UserForm\r\n    Range(\"A\" & m_lngRow).Select\r\n    Me.txtName = Range(\"A\" & m_lngRow)\r\nEnd Sub\r\n\r\n\r\nPrivate Sub btnBack_Click()\r\n    \r\n    'increment the active row and sync the UserForm and the Activesheet\r\n    m_lngRow = ActiveCell.Row - 1\r\n    \r\n    'select the next cell and render the new data on the UserForm\r\n    Range(\"A\" & m_lngRow).Select\r\n    Me.txtName = Range(\"A\" & m_lngRow)\r\n    \r\nEnd Sub\r\n\r\nPrivate Sub btnEnter_Click()\r\n    Range(\"A\" & ActiveCell.Row) = Me.txtName\r\n    \r\nEnd Sub\r\n\r\n\r\n\r\n\r\nPrivate Sub btnNew_Click()\r\n    Dim lngLastRow As Long\r\n    \r\n    'Find the last row used\r\n    lngLastRow = FindLastRow(\"A\")\r\n    \r\n    lngLastRow = lngLastRow + 1\r\n    Range(\"A\" & lngLastRow).Select\r\n    Me.txtName = Range(\"A\" & lngLastRow)\r\nEnd Sub\r\n\r\n\r\n\r\n\r\nFunction FindLastRow(WhichColumn As String) As Long\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\nPrivate Sub UserForm_Initialize()\r\n    Range(\"A2\").Select\r\n    Me.txtName = Range(\"A2\")\r\nEnd Sub\r\n\r\n\r\n<\/pre>\n
So that’s how to find the last row in Excel VBA.  <\/p>\n
Let me know if you have any questions.<\/p>\n
Use this form to send me a message:<\/p>\n
[simple_contact_form]<\/p>\n
****************************************************<\/p>\n\n\n
\n
<\/div>\n