Meaning of Cells Rows Count A End xlUp row

Question

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

User · Answer

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   amp  Rows Count  End xlUp  for compatibility reasons across workbooks with different numbers of rows

User · Answer

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

User · Answer

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

User · Answer

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

[vba] Meaning of .Cells(.Rows.Count,"A").End(xlUp).row

Examples related to vba

Examples related to excel