CODEWITHSUNDEEP
excelvba
HalfCrazy
HalfCrazy

Reputation: 3

Finding Last Row

I'm writing a macro to update key cells from a form, then write the data to a table in a second sheet. To do this, I'm using a For i = 1 to LR to match, then write the Row number to a variable if it finds a match.

However the code to find the last row, and therefore the length of the loop isn't working as expected.

Dim LR as Long

LR = Cells(Workbooks("Tracking Sheet.xlsx").Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row 

Debug.Print LR

I've tried pre-populating both less than 25 and more than 25 (e.g. 38) , but the LR variable is always showing 25 in the Immediate window. In the immediate window,

?Cells(Workbooks("Tracking Sheet.xlsx").Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row

returns 38, as it should.

LR = Cells(Workbooks("Tracking Sheet.xlsx").Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row

?LR

also returns 38

I just don't get it...

Upvotes: 0

Views: 945

Answers (2)

Tim Stack
Tim Stack

Reputation: 3248

Maybe the code explained will help?

LRow = ThisWorkbook.Sheets(1).Cells(ThisWorkbook.Sheets(1).Rows.Count, "A").End(xlUp).Row

Let's break up this code. First, we place it in a With statement to save us some typing

With ThisWorkbook.Sheets(1)
    LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Let's break this up in pieces. First, there is .Cells(.Rows.Count, "A")
This is a reference to a single cell. Specifically, the very last row in column A of ThisWorkbook.Sheets(1). Rows.Count gives the last row number in the Excel sheets. Recent Excel versions support 1,048,576 rows, but e.g. Excel 97 only supported 65536 rows.
In short, .Cells(.Rows.Count, "A") is for recent excel versions equal to (A1048576)

Then End(xlUp). This does the same as when you hold CTRL and press the arrow up button on your keyboard. This brings Excel to the first filled row from the bottom.

Finally .Row, which returns the row number of this found cell.

You could also start looking from the the top. Try holding CTRL and then pressing the arrow down button. This would stop at the last non-empty cell. However, it would also stop when you have blank cells between filled cells. So, not always a preferred method. The code would be

With ThisWorkbook.Sheets(1)
    LRow = .Cells(1, "A").End(xlDown).Row
End With

Upvotes: 0

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11998

Try this:

Dim LR As Long

With Application.Workbooks("Tracking Sheet.xlsx").Worksheets("Sheet1")

LR = .Range("A" & .Rows.Count).End(xlUp).Row
End With

Upvotes: 2

Related Questions