Set Public Variable on Open
 

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

Set Public Variable on Open
 

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


"jlclyde" wrote:

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

Set Public Variable on Open
 

On Sep 11, 12:20*pm, Gary''s Student
wrote:
Dim r As Range
Set r = Sheets("Sheet1").UsedRange
nLastRow = r.Rows.Count + r.Row - 1
MsgBox ("last row " & nLastRow)
--
Gary''s Student - gsnu200903



"jlclyde" wrote:
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- Hide quoted text -

- Show quoted text -

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

Set Public Variable on Open
 

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


"jlclyde" wrote:

On Sep 11, 12:20 pm, Gary''s Student
wrote:
Dim r As Range
Set r = Sheets("Sheet1").UsedRange
nLastRow = r.Rows.Count + r.Row - 1
MsgBox ("last row " & nLastRow)
--
Gary''s Student - gsnu200903



"jlclyde" wrote:
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- Hide quoted text -

- Show quoted text -

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

Set Public Variable on Open
 

On Sep 11, 12:47*pm, Gary''s Student
wrote:
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



"jlclyde" wrote:
On Sep 11, 12:20 pm, Gary''s Student
wrote:
Dim r As Range
Set r = Sheets("Sheet1").UsedRange
nLastRow = r.Rows.Count + r.Row - 1
MsgBox ("last row " & nLastRow)
--
Gary''s Student - gsnu200903

"jlclyde" wrote:
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- Hide quoted text -

- Show quoted text -

This will set a variable as public to be used in multiple macros?- Hide quoted text -

- 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`