Set Public Variable on Open

[jlclyde]

Can you set a variable when a workbook opens? I have been trying to
set a LstRow as long variabel to be used by multiple modules on open
and I cna not seem to get it to work. Here is what I have in the
workbook module. I have many macros that call out for LstRow and
assumed it woudl be easier to set it in one shot and then call it crom
each of the macros going forward.

Option Explicit
Public LstRow As Long

Private Sub Workbook_Open()
LstRow = Sheet1.UsedRange.Rows.Count
End Sub

Thanks,
Jay

 

[Gary''s Student]

Dim r As Range
Set r = Sheets("Sheet1").UsedRange
nLastRow = r.Rows.Count + r.Row - 1
MsgBox ("last row " & nLastRow)

 

[jlclyde]

Dim r As Range
Set r = Sheets("Sheet1").UsedRange
nLastRow = r.Rows.Count + r.Row - 1
MsgBox ("last row " & nLastRow)
--
Gary''s Student - gsnu200903








- Show quoted text -

This will set a variable as public to be used in multiple macros?

 

[Gary''s Student]

Just be sure to DIM it in a standard module ABOVE any subs or functions and
DIM it as Public:

Public nLastRow as Long

By DIMming it OUTSIDE any subs (normal or event) it will be both public and
static

 

[jlclyde]

Just be sure to DIM it in a standard module ABOVE any subs or functions and
DIM it as Public:

Public nLastRow as Long

By DIMming it OUTSIDE any subs (normal or event) it will be both public and
static
--
Gary''s Student - gsnu200903






- Show quoted text -

When I go to another module and run the macros that call out the
LstRow, LstRow is = 0. Even though it is set when the workbook
opens. Why is this and how do I get the LstRow to = the last used
Range.row for all macros and functions?

Thanks,
Jay`