find last row of data in book1

0

I am running this code from another workbook and want to

get the last row with data in book1 that has data in a1 - a15

the code runs and returns lrow=6  not 15

what have I done wrong?  Thanks

[CODEPrivate Sub CommandButton8_Click()
'RELOAD NEW DATA FOR EACH STORE FROM ASCEND QUERY REPORTS
Dim LROW As Long
'RELOAD NEW DATA RICHARDSON
Workbooks("Book1.xlsx").Sheets("Sheet1").Activate
Workbooks("book1.xlsx").Sheets("sheet1").Select
   With Workbooks("book1.XLSX").Worksheets("sheet1")
      LROW = Range("a1").End(xlDown).Row
      MsgBox " book1 LROW = " & LROW
    End With
End Sub[/CODE]

Answer
Discuss

Answers

0
Selected Answer

First to answer your question, this is what you did wrong:-

 LROW = Range("a1").End(xlDown).Row

Although the line is within the With block, the range referred to isn't linked to it.  Range("a1") is on the ActiveSheet because no other sheet is specified. In order to link the range to the preceding With statement it must be preceded by a period, thus:-

With Workbooks("book1.XLSX").Worksheets("sheet1")
      LROW = .Range("a1").End(xlDown).Row

With that said, xlDown will look for the first blank row from the top down. xlUp will search for the first used row from bottom up. Specify the last cell as Cells(Rows.Count, "A"), both linked to the worksheet on which you want the last row. Therefore,

With Workbooks("book1.XLSX").Worksheets("sheet1")
      LRow = .Cells(.Rows.Count, "A").End(xlUp).Row

Bear in mind that "Activate" and "Select" are required only to tell Excel what to show on the screen. You can access any open workbook (open any closed workbook) without activating or selecting anything.

Private Sub CommandButton8_Click()
    'RELOAD NEW DATA FOR EACH STORE FROM ASCEND QUERY REPORTS
    Dim Ws As Worksheet
    Dim LRow As Long

    'RELOAD NEW DATA RICHARDSON
    Set Ws = Workbooks("Book1.xlsx").Sheets("Sheet1")
    With Ws
        LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        MsgBox "Book1 Last Row = " & LRow
     End With
End Sub

Observe that the declaration of Ws isn't strictly required.
Set Ws = Workbooks("Book1.xlsx").Sheets("Sheet1")
    With Ws
could be combined into
With Workbooks("Book1.xlsx").Sheets("Sheet1")
Newbies tend to be relieved that Excel can guess their wish. By the time they have become old hands at programming they prefer to tell Excel what to do. It's a bit like riding a horse. The horse knows its way home but it isn't always home where you want to go.

Discuss

Discussion

Thank you for explaining your answer.  I am a newbie and learning slowly
carroll (rep: 14) Feb 5, '19 at 12:21 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login