Code not working
in [Programming]
|
From: JMay on 16 Mar 2010 14:14 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
From: JLGWhiz on 16 Mar 2010 14:37 P.S. If you still have this in another code module, delete it. "JMay" <JMay(a)discussions.microsoft.com> wrote in message news:31740148-86B6-48F3-BD47-67C4539F6859(a)microsoft.com... >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
From: JLGWhiz on 16 Mar 2010 14:36 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" <JMay(a)discussions.microsoft.com> wrote in message news:31740148-86B6-48F3-BD47-67C4539F6859(a)microsoft.com... >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
From: Gary Brown on 16 Mar 2010 14:52 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
From: Gord Dibben on 16 Mar 2010 15:11
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 <JMay(a)discussions.microsoft.com> 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 |