Simple question - Simple Solution - Last Row

erutherford

Active Member
Joined
Dec 19, 2016
Messages
342
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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

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
342
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,688
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
342
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,688
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Where's the code to populate Lastrow?
 

erutherford

Active Member
Joined
Dec 19, 2016
Messages
342
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,688
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.
 

Forum statistics

Threads
1,089,542
Messages
5,408,856
Members
403,236
Latest member
shwetha97

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top