Use Lastrow found as a Public Constant?

[ArnMan]

Hello,

I am wondering how I can add my lRow as a public const so I don't have the same code in every module doing the same thing

This is what I am using to get my last row used and works great.

    Dim lRow As Long
    lRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row + 1

I am hoping to add this as a Public const so all I have to do is just add lRow to whatever cell or range I am working on at the time.
I've tried this

Public Const lRow As Long = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row + 1

But vba doesn't like that. it highlights the .Row for some reason.

Thank you in advance.

 

[Rick Rothstein]

Re: How can I use Lastrow found as a Public Constant?

Public Const lRow As Long = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row + 1

But vba doesn't like that. it highlights the .Row for some reason.

You cannot include any function or property calls when defining a constant as they need to be evaluated before a value is returned from them. The way constants work, as I understand it, is the constant value physically replaces the constant names in the code as a first step before the compiler begins doing its work, so the function or property does not have a value at that point in time.

 

[hiker95]

Re: How can I use Lastrow found as a Public Constant?

ArnMan,

See if the following will work for you:

Option Explicit

Public lRow As Long

Sub Test()

lRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row + 1

End Sub

 

[ArnMan]

Re: How can I use Lastrow found as a Public Constant?

Thank you for the quick response. I put that in a seperate module, and used Call Test where it is needed. That way I only need to use that instead of having to retype or copy and Paste my code in all the other modules I have going

ArnMan,

See if the following will work for you:

Option Explicit

Public lRow As Long

Sub Test()

lRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row + 1

End Sub

 

[ArnMan]

Re: How can I use Lastrow found as a Public Constant?

Thank you that actually explained it easy enough for me to understand what was going

Thank you again

You cannot include any function or property calls when defining a constant as they need to be evaluated before a value is returned from them. The way constants work, as I understand it, is the constant value physically replaces the constant names in the code as a first step before the compiler begins doing its work, so the function or property does not have a value at that point in time.

 

[JackDanIce]

Re: How can I use Lastrow found as a Public Constant?

I tend to use the following public function to return the Last Row of a specified sheet and column, which I can then call from any module, removing need for a public constant:

Public Function LastRow(ByRef wks As Worksheet, Optional ByRef col As Long = 1) As Long
    
    With wks
        LastRow = .Cells(.Rows.count, col).End(xlUp).row
    End With

End Function

Then in any code, I can use:

Sub Dummy()

Dim LR as Long
Dim wks as Worksheet

Set wks = Sheets("Sheet1")

LR = LastRow(wks, 2)

End Sub

Here function LastRow returns the row number of the last used cell in column 2 (B) to variable LR within Sub Dummy. If I have another sub, I can call the same function again.

If I use

LR = LastRow(wks)

it defaults the column to check as 1 (A)

 

[hiker95]

Re: How can I use Lastrow found as a Public Constant?

Thank you for the quick response. I put that in a seperate module, and used Call Test where it is needed. That way I only need to use that instead of having to retype or copy and Paste my code in all the other modules I have going

ArnMan,

Thanks for the feedback.

You are very welcome.

Nicely done.

And, come back anytime.