I need to capture the Last Row number as soon as the WB opens; And as the
sheet2 Column A cells are filled-in have the variable Lrow furnish an
up-to-date
value, representing the last non-blank row in Col A of Sheet2. The below is
not working; Can yo suggest a better way?

TIA,

Dim Lrow As Long
Private Sub Workbook_Open()
Lrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
End Sub

Press Alt + F11 to open the VB editor. In the Projects pane at the upper
left side of the screen, double click on ThisWorkbook and paste the code
into the code window that opens. Be sure that the title bar show
ThisWorkbook(Code) when you paste.

Private Sub Workbook_Open()
Dim Lrow As Long
Lrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
End Sub




"JMay" wrote in message
...
I need to capture the Last Row number as soon as the WB opens; And as the
sheet2 Column A cells are filled-in have the variable Lrow furnish an
up-to-date
value, representing the last non-blank row in Col A of Sheet2. The below
is
not working; Can yo suggest a better way?

TIA,

Dim Lrow As Long
Private Sub Workbook_Open()
Lrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
End Sub

P.S. If you still have this in another code module, delete it.


"JMay" wrote in message
...
I need to capture the Last Row number as soon as the WB opens; And as the
sheet2 Column A cells are filled-in have the variable Lrow furnish an
up-to-date
value, representing the last non-blank row in Col A of Sheet2. The below
is
not working; Can yo suggest a better way?

TIA,

Dim Lrow As Long
Private Sub Workbook_Open()
Lrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
End Sub

1) create a Module in the Visual Basic Editor (VBE)
- INSERT MODULE

2) put the following line of code at the top of the module in order to make
it's SCOPE so that all modules will know it's value...
Public Lrow As Long

3) in the 'ThisWorkbook' module, put the following code...
Private Sub Workbook_Open()
Lrow = Worksheets("Sheet2").Cells.SpecialCells(xlLastCell ).Row
End Sub

4) in the worksheet "Sheets2" module, put the following code...
Private Sub Worksheet_Change(ByVal Target As Range)
Lrow = Worksheets("Sheet2").Cells.SpecialCells(xlLastCell ).Row
End Sub

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"JMay" wrote:

I need to capture the Last Row number as soon as the WB opens; And as the
sheet2 Column A cells are filled-in have the variable Lrow furnish an
up-to-date
value, representing the last non-blank row in Col A of Sheet2. The below is
not working; Can yo suggest a better way?

TIA,

Dim Lrow As Long
Private Sub Workbook_Open()
Lrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
End Sub

Thanks Gary;;;

"Gary Brown" wrote:

1) create a Module in the Visual Basic Editor (VBE)
- INSERT MODULE

2) put the following line of code at the top of the module in order to make
it's SCOPE so that all modules will know it's value...
Public Lrow As Long

3) in the 'ThisWorkbook' module, put the following code...
Private Sub Workbook_Open()
Lrow = Worksheets("Sheet2").Cells.SpecialCells(xlLastCell ).Row
End Sub

4) in the worksheet "Sheets2" module, put the following code...
Private Sub Worksheet_Change(ByVal Target As Range)
Lrow = Worksheets("Sheet2").Cells.SpecialCells(xlLastCell ).Row
End Sub

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"JMay" wrote:

I need to capture the Last Row number as soon as the WB opens; And as the
sheet2 Column A cells are filled-in have the variable Lrow furnish an
up-to-date
value, representing the last non-blank row in Col A of Sheet2. The below is
not working; Can yo suggest a better way?

TIA,

Dim Lrow As Long
Private Sub Workbook_Open()
Lrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
End Sub

How do you know it is not working?

Dim Lrow As Long
Private Sub Workbook_Open()
Lrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
MsgBox Lrow
End Sub

Gives me a message of last row number in Sheet2 which just happens to be 32

But you won't get an update as you fill-in rows in column A unless you run
again.

To keep updating Lrow for use somewhere you could add event code to Sheet2

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lrow As Long
Lrow = Cells(Rows.Count, "A").End(xlUp).Row
MsgBox Lrow
End Sub


Gord Dibben MS Excel MVP

On Tue, 16 Mar 2010 11:14:01 -0700, JMay
wrote:

I need to capture the Last Row number as soon as the WB opens; And as the
sheet2 Column A cells are filled-in have the variable Lrow furnish an
up-to-date
value, representing the last non-blank row in Col A of Sheet2. The below is
not working; Can yo suggest a better way?

TIA,

Dim Lrow As Long
Private Sub Workbook_Open()
Lrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
End Sub