You are talking about scope of a variable. Within a macro the lastrow variable will remain in scope (that is retain it's value until the End Sub statement). If you want to change the lastrow value within the macro that is fine - for example
Sub Test()
dim lastrow as long
lastrow = Worksheets("Sheet1").cells(rows.count,"A").end(xlup).row
'etc etc do macro work
'you can then reset the value of lastrow within the macro
lastrow = lastrow +1
'etc etc
lastrow = Worksheets("Sheet2").cells(rows.count."B").end(xlup).row
End Sub
If you want to make lastrow available to multiple subs then declare it as a public variable - so it is declared OUTSIDE the sub
Public lastrow as long
Sub test2()
lastrow = worksheets("Sheet1").cells(rows.count, "A").end(xlup).row
'etc etc rest of macro
Msgbox "The value of lastrow is " & lastrow
End Sub
Sub test3()
msgbox "The value of lastrow is " & lastrow ' this will be the same value as it was set in Sub test2
End Sub
So the variable if declared as a public variable it will be available to all the macros - if the variable is declared within a sub the the scope is confined to the macro it is declared in and will not be available to other subs - that is the variable goes out of scope as soon as End Sub is reached and it's value will be lost and unavailable to other subs.
In summary - The scope of a variable depends on where it is declared and how it is declared. You can change the value of lastrow as many times as you want - Excel does not suffer from confusion just because you assign a new value to the variable. Google Variable Scope in VBA and you will get more information.
Is there a way to 'de-active' a lastrow statement, so one can be sure it is not going to mess up the macro further on down?
If you no longer need to use lastrow in the macro there is no need to "deactivate it" which I assume you mean set lastrow = 0
Sub Test()
dim lastrow as long
lastrow = Worksheets("Sheet1").cells(rows.count,"A").end(xlup).row
'etc etc do macro work
lastrow = 0
In the VB editor there is an option to add a watch - highlight the variable and right click and select Add Watch - this will add the variable to the Watches window and you can see how the variable changes. When you go to a new sub you will see it will Go Out Of Context unless declared as a public variable. Instead of declaring a public variable you can also pass it to another sub e.g
Sub Test()
dim lastrow as long
lastrow = Worksheets("Sheet1").cells(rows.count,"A").end(xlup).row
call recheckValues(lastrow)
End Sub
Sub recheckValues(lastrow as long)
msgbox "The value for lastrow is " & lastrow
End Sub
So you don't have to declare the Public variable but just pass it as a parameter to another sub and this way the value of lastrow can be used in another sub without having to recalculate it. Hope that all made sense. As I said - Google variable scope in VBA - remember Google is your friend
Bookmarks