Code not working

J

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
 
J

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
 
G

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
 
G

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
 
J

JMay

Thanks Gary;;;

Gary Brown said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top