Can't write code to find last data row in excel user form

I'm trying to build a user form for an Excel worksheet. I’m having trouble getting the user form to find the “last row” of data.

I've been following a tutorial I found online. It seems pretty comprehensive and I’ve been following all the steps. For the purpose of the exercise, I’ve been told to
"add the constant LastRow to the start of the user form module as follows:

Const LastRow = 20

while testing this routine” The lesson says that “Later, you'll see how to determine the real last row of data in the worksheet, and you'll convert this constant to a module level variable.”

I followed all the other steps for setting up the code for the form command buttons “First, Next, and Previous”,and everything was working fine (all the data from the worksheet was displayed in the proper text boxes on the user form as I pressed the above command buttons)

The problem came when I followed the directions for adding code to the “Last” command button. The lesson says:
“To make the last row dynamic, a few changes need to be made to the program. First the LastRow constant needs to be switched to a variable like this:”

Private Sub UserForm_Initialize()

	GetData

	End Sub

So far so Good. The next step is to add the following code to find the last row:

Private Function FindLastRow()

	Dim r As Long

	r = 2
	Do While r < 65536 And Len(Cells(r, 1).Text) > 0
    		r = r + 1
    
	Loop

	FindLastRow = r

	End Function

At this point I am still able to navigate through the data rows via the user form, but the “Last” command button still does not function (won’t take me to the last row)

Here’s where the trouble is. The lesson then says “Then you can set the LastRow variable by adding the following line to the UserForm_Initialize event.

LastRow = FindLastRow

I take this to mean the following:

Private Sub UserForm_Initialize()

	LastRow = FindLastRow
	GetData

	End Sub

When I try this I get an error message “Compile error, Assignment to constant not permitted” I assume there is a conflict here with the temporary “Const LastRow = 20” that I was told to enter at the beginning.

When I delete the “Const LastRow = 20” and run the user form I get an error message “Invalid row number” and no data is displayed in the form, and I am not able to navigate through the data.

I need to resolve this in order to make data entry possible with this form.

Am I missing something obvious, or is there a problem with the directions in the lesson?

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

For starters, replace the line:
Const LastRow = 20, with:
Dim LastRow As Long

I wouldn't use a Loop to find the next row

Option Explicit


Private Sub UserForm_Initialize()
    MsgBox NextRow(Sheet2)
End Sub

Function NextRow(Optional sht As Worksheet) As Long
    If sht Is Nothing Then Set sht = ActiveSheet
    With sht
        NextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
    End With
End Function

I think you are confusing NextRow and LastRow. Your button for "last" is supposed to take you to the last record in your sheet. The nextrow will be the blank row just below it where the new record will be entered. RoyUK gave you code for that next row:

Function NextRow(Optional sht As Worksheet) As Long
    If sht Is Nothing Then Set sht = ActiveSheet
    With sht
        NextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
    End With
End Function

Notice that the line: NextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1, finds the last used row and adds 1 to it to move to the blank line below it to insert the new record.

Your form Initializer:

Private Sub UserForm_Initialize()
	LastRow = FindLastRow
	GetData
End Sub

is ok (notice that you are looking for the last used row/record in this case). Therefore you must modify the Roy's code to replace your your looping code:

Function FindLastRow(Optional sht As Worksheet) As Long
    If sht Is Nothing Then Set sht = ActiveSheet
    With sht
        FindLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
End Function

Since this is the row number of the last record, you must remember to add 1 to that number when you want to insert a new record at the end of your database.