Simple question - Simple Solution - Last Row

erutherford

Active Member
Joined
Dec 19, 2016
Messages
348
I have a table with headers (starting in A1), nothing fancy. I have a form that controls the data that is entered, works fine.

If the table is empty with only the headers there, I get an error (at the Range statement). If cell A2 has something in it it works fine and finds the last row.

Code:
Private Sub CommandButton1_Click()
Range("A1").End(xlDown).Offset(1, 0).Select
'FN
If TextBox1.Value = "" Then
 
    MsgBox "First Name Required!", vbCritical
    UserForm1.TextBox1.SetFocus
    Exit Sub
 
End If

'LN
If TextBox2.Value = "" Then
 
    MsgBox "Last Name Required!", vbCritical
    UserForm1.TextBox2.SetFocus
    Exit Sub

End If

'US - Enters Data to Table
   
ActiveCell = TextBox1.Value 'FN
ActiveCell.Offset(0, 1) = TextBox2.Value 'LN

Unload UserForm2

End Sub
has to be something simple and I am looking right at it! Just don't see it!
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,761
If I've understood correctly, this might be what you're after...

Code:
Private Sub CommandButton1_Click()
Dim LastRow As Long
 If TextBox1.Value = "" Then
    MsgBox "First Name Required!", vbCritical
    UserForm1.TextBox1.SetFocus
    Exit Sub
End If
If TextBox2.Value = "" Then
    MsgBox "Last Name Required!", vbCritical
    UserForm1.TextBox2.SetFocus
    Exit Sub
End If
LastRow = Cells(Rows.Count, 1).End(xlUp).Offset(1)
Cells(LastRow, 1) = TextBox1.Value
Cells(LastRow, 2) = TextBox2.Value
 Unload UserForm2
 End Sub
 

erutherford

Active Member
Joined
Dec 19, 2016
Messages
348
found this code which seems to have solved the issue

<code>
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
</code>

Counting up is a better fit, than counting down.

Joe4 posted this explanation:

Is there any data in column A past row 1 to start with?
If not, Range("A1").End(xldown) will take you down to your last available row on your worksheet, but then the Offset(1,0) will try to take you down one more row, which is impossible if you are on the last possible row already.

Perhaps this version will work better (it works from the bottom up rather than the top down)?
Cells(Rows.Count,"A").End(xlUp).Offset(1,0).Select
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,692
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Neil's code did exactly the same thing, except it avoided inefficiently selecting, so it should have produced the same result.
 

erutherford

Active Member
Joined
Dec 19, 2016
Messages
348
Rory
I did read where "select" is not the best way to go. But the "cells(LastRow)" creates an error at the first line ('FN)

Code:
 MsgBox "Information Complete, Thank You"
    Unload UserForm1

'US - Enters Data to Table

Cells(LastRow, 1) = TextBox1.Value 'FN
Cells(LastRow, 2) = TextBox2.Value 'LN
Cells(LastRow, 3) = TextBox3.Value 'Add1
Cells(LastRow, 4) = TextBox4.Value 'Add2
Cells(LastRow, 5) = TextBox5.Value 'Add3
Cells(LastRow, 6) = TextBox6.Value 'City
Cells(LastRow, 7) = TextBox7.Value 'State
Cells(LastRow, 8) = TextBox8.Value 'Zip
Cells(LastRow, 9) = TextBox9.Value 'Country
Cells(LastRow, 10) = TextBox10.Value 'Email
Cells(LastRow, 11) = TextBox11.Value 'Ph1
Cells(LastRow, 12) = TextBox12.Value 'Ph2
Cells(LastRow, 13) = DTPicker1.Value 'Start Time
Cells(LastRow, 14) = DTPicker2.Value 'End Tme
Cells(LastRow, 15) = TextBox13.Value 'Notes
Time to do some investigating I guess
 
Last edited by a moderator:

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,761
What error were you getting with my code?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,692
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Where's the code to populate Lastrow?
 

erutherford

Active Member
Joined
Dec 19, 2016
Messages
348
Rory & Neil,
Here is the entire code for the command button. It just a basic contact form.

Code:
Private Sub CommandButton1_Click()

'FN
If TextBox1.Value = "" Then
 
    MsgBox "First Name Required!", vbCritical
    UserForm1.TextBox1.SetFocus
    Exit Sub
 
End If

'LN
If TextBox2.Value = "" Then
 
    MsgBox "Last Name Required!", vbCritical
    UserForm1.TextBox2.SetFocus
    Exit Sub

End If

'Add1
 If TextBox3.Value = "" Then
 
    MsgBox "Address 1 Required!", vbCritical
    UserForm1.TextBox3.SetFocus
    Exit Sub
 
End If
'Add2
If TextBox4.Value = "" Then
 
    MsgBox "Address 2Required!", vbCritical
    UserForm1.TextBox4.SetFocus
    
    Exit Sub

End If
    
'Add3
If TextBox5.Value = "" Then

    MsgBox "Not Required", vbInformation
 
    Exit Sub
 
End If
 
'City
If TextBox6.Value = "" Then
 
    MsgBox "City Required!", vbCritical
    UserForm1.TextBox6.SetFocus
    
    Exit Sub
End If
 
'State
If TextBox7.Value = "" Then
 
    MsgBox "State Required!", vbCritical
    UserForm1.TextBox7.SetFocus
    
    Exit Sub
End If
    
'Zip
If TextBox8.Value = "" Then
 
    MsgBox "Zip Code Required!", vbCritical
    UserForm1.TextBox8.SetFocus
    
    Exit Sub

End If

'Country
If TextBox9.Value = "" Then
 
    MsgBox "Country Required!", vbCritical
    UserForm1.TextBox9.SetFocus
    
    Exit Sub
 
End If
 
'Email
If TextBox10.Value = "" Then
 
    MsgBox "Email Required!", vbCritical
    UserForm1.TextBox10.SetFocus
    
    Exit Sub
    
End If

'Ph1
If TextBox11.Value = "" Then
 
    MsgBox "Phone Number Required!", vbCritical
    UserForm1.TextBox11.SetFocus
    Exit Sub
 
End If
 
'Ph2
If TextBox12.Value = "" Then
    
    MsgBox "Not Required", vbInformation
    UserForm1.TextBox12.SetFocus
    Exit Sub
    
End If

'Start Time
If DTPicker1.Value = "" Then
 
    MsgBox "Start Time Required!", vbCritical
    UserForm1.DTPicker1.SetFocus
    Exit Sub
 
End If
 
'End Time
If DTPicker2.Value = "" Then
 
    MsgBox "End Time Required!", vbCritical
    UserForm1.DTPicker2.SetFocus
    Exit Sub
    
End If

'Notes
If TextBox13.Value = "" Then
    
    MsgBox "Not Required", vbInformation
    Exit Sub
 
End If
    
    MsgBox "Information Complete, Thank You"
    

'US - Enters Data to Table


Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select

ActiveCell = TextBox1.Value 'FN
ActiveCell.Offset(0, 1) = TextBox2.Value 'LN
ActiveCell.Offset(0, 2) = TextBox3.Value 'Add1
ActiveCell.Offset(0, 3) = TextBox4.Value 'Add2
ActiveCell.Offset(0, 4) = TextBox5.Value 'Add3
ActiveCell.Offset(0, 5) = TextBox6.Value 'City
ActiveCell.Offset(0, 6) = TextBox7.Value 'State
ActiveCell.Offset(0, 8) = TextBox8.Value 'Zip
ActiveCell.Offset(0, 9) = TextBox9.Value 'Country
ActiveCell.Offset(0, 10) = TextBox10.Value 'Email
ActiveCell.Offset(0, 11) = TextBox11.Value 'Ph1
ActiveCell.Offset(0, 12) = TextBox12.Value 'Ph2
ActiveCell.Offset(0, 13) = DTPicker1.Value 'Start Time
ActiveCell.Offset(0, 14) = DTPicker2.Value 'End Tme
ActiveCell.Offset(0, 15) = TextBox13.Value 'Notes

Unload UserForm2

End Sub
Neil when I remove the "Active cell" code and replace with "cells(LastRow)" and run it the first line is highlighted yellow (Cells(LastRow, 1) = TextBox1.Value 'FN) and data is not transferred.
Rory the code to populate starts after "'US - Enters Data to Table" if I understand your question.
 
Last edited by a moderator:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,692
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
when I remove the "Active cell" code and replace with "cells(LastRow)" and run it the first line is highlighted yellow (Cells(LastRow, 1) = TextBox1.Value 'FN) and data is not transferred.
It would, if you don't add the line from Neil's earlier post to assign a value to Lastrow.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,763
Messages
5,410,281
Members
403,306
Latest member
ekastan

This Week's Hot Topics

Top