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