Code not working

[JMay]

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

 

[JLGWhiz]

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

 

[JLGWhiz]

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

 

[Gary Brown]

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

 

[Gord Dibben]

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

 

[JMay]

Thanks Gary;;;

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