# Meaning of .Cells(.Rows.Count,”A”).End(xlUp).row

###### Posted By: Anonymous

I was just wondering if you could help me better understand what `.Cells(.Rows.Count,"A").End(xlUp).row`

does. I understand the portion before the `.End`

part.

## Solution

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 worksheet

Then 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:

- If the range is multiple cells, it will use the region of
`rng.cells(1,1)`

. - If the range isn’t in a region, or the range is already at the end of the region, then it will travel along the direction until it enters a region and return the first encountered cell in that region.
- If it encounters the edge of the worksheet it will return the cell on the edge of that worksheet.

So `Range.End`

is not a trivial function.

`.row`

returns the row index of that cell.

###### Answered By: Anonymous

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.