Help with lastrow in VBA

NPK

Board Regular
Joined
Jun 5, 2008
Messages
57
Code:
Private Sub CommandButton2_Click()
LastRow = Range("A65536").End(xlUp).Row
LastRow = LastRow + 1
Sheet1.Range("A" & LastRow) = Calendar1.Value
    Sheet1.Range("A" & LastRow).NumberFormat = "dd-mmm-yyyy"
Sheet1.Range("b" & LastRow).Value = TextBox1.Value
Sheet1.Range("c" & LastRow).Value = TextBox2.Value
Sheet1.Range("d" & LastRow).Value = TextBox3.Value
Sheet1.Range("e" & LastRow).Value = TextBox5.Value
Sheet1.Range("f" & LastRow).Value = TextBox6.Value
Sheet1.Range("g" & LastRow).Value = TextBox7.Value
Sheet1.Range("h" & LastRow).Value = TextBox4.Value
Call SortAscending
Unload Me
End Sub

I'm butchering VBA, but I have to start somewhere.

I've got a userform with a command button on sheet4 that inserts input values into cells on a sheet1 once the "OK" button is clicked. I then sort the data via the date in column A via the Macro "SortAscending" that is just a recorded macro using the sort ascending button on the toolbar.

The data selects the last row that data was entered into and overwrites the data in the row rather than finding the next open row to place the data in.

Thanks.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Rather than defining LastRow twice, try replacing the second "LastRow=" with "NextRow=LastRow+1" then use the variable NextRow instead of LastRow in your code
 
Upvote 0
Code:
LR = Cells(Rows.Count, 1).End(xlUp).Row + 1
Then
Code:
Sheet1.Cells(LR, "B") = Textbox1.Value

lenze
 
Upvote 0
Rather than defining LastRow twice, try replacing the second "LastRow=" with "NextRow=LastRow+1" then use the variable NextRow instead of LastRow in your code

Thanks for your input. I put this in as shown below. The data enters in row 2 only, and overwrites whatever data is there.

Code:
Private Sub CommandButton2_Click()
LastRow = Range("A65536").End(xlUp).Row
NextRow = LastRow + 1
Sheet1.Range("A" & NextRow) = Calendar1.Value
    Sheet1.Range("A" & NextRow).NumberFormat = "dd-mmm-yyyy"
Sheet1.Range("b" & NextRow).Value = TextBox1.Value
Sheet1.Range("c" & NextRow).Value = TextBox2.Value
Sheet1.Range("d" & NextRow).Value = TextBox3.Value
Sheet1.Range("e" & NextRow).Value = TextBox5.Value
Sheet1.Range("f" & NextRow).Value = TextBox6.Value
Sheet1.Range("g" & NextRow).Value = TextBox7.Value
Sheet1.Range("h" & NextRow).Value = TextBox4.Value
Unload Me
End Sub
 
Upvote 0
Code:
LR = Cells(Rows.Count, 1).End(xlUp).Row + 1
Then
Code:
Sheet1.Cells(LR, "B") = Textbox1.Value

lenze


I tried this troughout and the values are still being placed in row 2. Could I have something conditional that is going on?? Thanks for the help.

Code:
Private Sub CommandButton2_Click()
LR = Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheet1.Cells(LR, "A") = Calendar1.Value
Sheet1.Cells(LR, "A").NumberFormat = "dd-mmm-yyyy"
Sheet1.Cells(LR, "B") = TextBox1.Value
Sheet1.Cells(LR, "C") = TextBox2.Value
Sheet1.Cells(LR, "D") = TextBox3.Value
Sheet1.Cells(LR, "E") = TextBox5.Value
Sheet1.Cells(LR, "F") = TextBox6.Value
Sheet1.Cells(LR, "G") = TextBox7.Value
Sheet1.Cells(LR, "H") = TextBox4.Value
Unload Me
End Sub
 
Upvote 0
Try this:
Code:
Private Sub CommandButton2_Click()
With Sheet1
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    .Range("A" & LastRow) = Calendar1.Value
    .Range("A" & LastRow).NumberFormat = "dd-mmm-yyyy"
    .Range("b" & LastRow).Value = TextBox1.Value
    .Range("c" & LastRow).Value = TextBox2.Value
    .Range("d" & LastRow).Value = TextBox3.Value
    .Range("e" & LastRow).Value = TextBox5.Value
    .Range("f" & LastRow).Value = TextBox6.Value
    .Range("g" & LastRow).Value = TextBox7.Value
    .Range("h" & LastRow).Value = TextBox4.Value
End With
Call SortAscending
Unload Me
End Sub
 
Upvote 0
Try this:
Code:
Private Sub CommandButton2_Click()
With Sheet1
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    .Range("A" & LastRow) = Calendar1.Value
    .Range("A" & LastRow).NumberFormat = "dd-mmm-yyyy"
    .Range("b" & LastRow).Value = TextBox1.Value
    .Range("c" & LastRow).Value = TextBox2.Value
    .Range("d" & LastRow).Value = TextBox3.Value
    .Range("e" & LastRow).Value = TextBox5.Value
    .Range("f" & LastRow).Value = TextBox6.Value
    .Range("g" & LastRow).Value = TextBox7.Value
    .Range("h" & LastRow).Value = TextBox4.Value
End With
Call SortAscending
Unload Me
End Sub

Good code. It works like a charm. Thank you.

Question, what part of the code logic causes this work differently than the other code? I see you have created a "With" portion.

Again, thank you.
 
Upvote 0
Your original code was this:
Code:
LastRow = Cells("A65536").End(xlUp).Row
because you did not specify the sheet that the Cells property referred to, it defaults to the activesheet, which presumably was not Sheet1. I just specified that the code should look at sheet1 to get the last used row.
 
Upvote 0
Your original code was this:
Code:
LastRow = Cells("A65536").End(xlUp).Row
because you did not specify the sheet that the Cells property referred to, it defaults to the activesheet, which presumably was not Sheet1. I just specified that the code should look at sheet1 to get the last used row.


Very good. Thank you. That is exactly what it was doing because the SortAscending macro returns the user to Sheet4, Cell A1.

Again, thank you.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top