Variable holding a value instead of recalculating based on parameters.

[Ataraxicatom]

Ok, I am using a simple line of code to grab a value for the last row with data on a sheet.

Dim LastRow As Integer
LastRow = ActiveSheet.UsedRange.Rows.Count

At first it appeared to work as intended, but it is holding a value and not recalculating when it is called again.
example: I push the button 5 times. It populates the information in the correct fileds. If I clear the fields and push the button again, it populates the information in the 6th spot and does nothing with the now blank 5 fields.
I may be wrong about this but I thought that even though I assigned it as a variable, when I called it a second time, it would recalculate based on the parameters assigned to it.
The plus side is that it will recognize that there is data in a field and skip it without overwriting it. But I need it to fill in blank cells if I delete them. I am going to attach my sheet so you can see what i am doing.

Cheers!

[TMS]

No, it is the fact that UsedRange is unreliable. However, it is reflecting what happens on a worksheet when you delete rows and press Ctrl-End.

You are better using:

LastRow = Range("A" & Rows.Count).End(xlUp).Row to get the last row

Change the A to whatever column will have the most rows, typically a key field.


Regards, TMS

[Medpack]

It's definitely not holding a value, and in fact, if you added MsgBox LastRow just after the part where you define it, you would see that the return is 0.

What do you want "LastRow" to find? If it's the LastRow in the column you can use

Cells(1, 1).End(xlDown).Row

[xladept]

It seems to work with this alteration:

Sheet4.Activate

Dim LastRow As Integer
If Range("A2") = "" Then
LastRow = 1
Else
LastRow = ActiveSheet.Range("A1").End(xlDown).Row
End If

[Ataraxicatom]

Thank you all for your help. I went with TMShucks' code variation, since it was a simple copy-paste-done scenario. To explain, I am making a single button to perform multiple tasks based on where it is located on a page (the row anyway). I am coding it with as much back end as possible, because there will be multiple users and I dont want someone deleting something and messing it up for everyone. Again, Thanks for all the help, I really appreciate it. Also, thanks for clarifying the problem I was having.

Cheers!

[TMS]

You're welcome. Thanks for the rep.

[xladept]

Hi Ataraxicatom,

You see now that code has ups and downs(!) and also thanks for the rep.

[Medpack]

Glad it's working