It is used to find the how many rows contain data in a worksheet that contains data in the column "A". The full usage is
lastRowIndex = ws.Cells(ws.Rows.Count, "A").End(xlUp).row
Where ws is a Worksheet object. In the questions example it was implied that the statement was inside a With block
With ws
lastRowIndex = .Cells(.Rows.Count, "A").End(xlUp).row
End With
ws.Rows.Count returns the total count of rows in the worksheet (1048576 in Excel 2010)..Cells(.Rows.Count, "A") returns the bottom most cell in column "A" in the worksheetThen there is the End method. The documentation is ambiguous as to what it does.
Returns a Range object that represents the cell at the end of the region that contains the source range
Particularly it doesn't define what a "region" is. My understanding is a region is a contiguous range of non-empty cells. So the expected usage is to start from a cell in a region and find the last cell in that region in that direction from the original cell. However there are multiple exceptions for when you don't use it like that:
rng.cells(1,1). So Range.End is not a trivial function.
.row returns the row index of that cell.The first part:
.Cells(.Rows.Count,"A")
Sends you to the bottom row of column A, which you knew already.
The End function starts at a cell and then, depending on the direction you tell it, goes that direction until it reaches the edge of a group of cells that have text. Meaning, if you have text in cells C4:E4 and you type:
Sheet1.Cells(4,"C").End(xlToRight).Select
The program will select E4, the rightmost cell with text in it.
In your case, the code is spitting out the row of the very last cell with text in it in column A. Does that help?
[A1].End(xlUp)
[A1].End(xlDown)
[A1].End(xlToLeft)
[A1].End(xlToRight)
is the VBA equivalent of being in Cell A1 and pressing Ctrl + Any arrow key. It will continue to travel in that direction until it hits the last cell of data, or if you use this command to move from a cell that is the last cell of data it will travel until it hits the next cell containing data.
If you wanted to find that last "used" cell in Column A, you could go to A65536 (for example, in an XL93-97 workbook) and press Ctrl + Up to "snap" to the last used cell. Or in VBA you would write:
Range("A65536").End(xlUp) which again can be re-written as Range("A" & Rows.Count).End(xlUp) for compatibility reasons across workbooks with different numbers of rows.
.Cells(.Rows.Count,"A").End(xlUp).row
I think the first dot in the parenthesis should not be there, I mean, you should write it in this way:
.Cells(Rows.Count,"A").End(xlUp).row
Before the Cells, you can write your worksheet name, for example:
Worksheets("sheet1").Cells(Rows.Count, 2).End(xlUp).row
The worksheet name is not necessary when you operate on the same worksheet.
Source: Stackoverflow.com