CODEWITHSUNDEEP
excelvbavba7
Namita Ojha
Namita Ojha

Reputation: 11

Difference between count and end(xlup)

I want to understand the difference between count and end(xlup) The purpose of both the expressions is to count number of cells with values in it! Then why are we using it together in the following code below:

Lastrowindex = cells(rows.count, "A"). End(xlUp).row

I had used count in one subroutine where I had selected the range and using count I found out how many cells contain data in it.

By googling the definition of end(xlup) it returned me same definition as for count i.e. to count the cells in our worksheet that have values in it.

When I tried the step to understand the output of Cells(rows.count, "A").shouldn't i get the count of cells with data in it?

Cells(rows.count, "A").End(xlUp).row

Upvotes: 1

Views: 694

Answers (2)

Alok
Alok

Reputation: 509

Let us say you want to count number of non empty cells in column A, for the sake of discussion assume you have data filled in cells from A1 to A25, there are two ways to do this counting as mentioned below:-

a) First method is you start counting from 1st cell i.e. A1 and keep moving down say to A2, A3, A4 till you reach the last non empty cell i.e. A25 => This is achieved by count function in Excel.

b) Second Method is you start counting from last cell allowed in Excel (e.g. in Excel 2019, last cell allowed is A1048576) and then keep moving upward i.e from cell A1048576 to 1048575 and then to 1048574 and so till you reach non empty cell i.e. A25 . This is achieved by using

Cells(rows.count, "A").End(xlUp).row

which is like telling Excel to first go to cell A1048576 which is returned by

 Cells(rows.count, "A")

and then move in upward direction

.End(xlUp).row

Hope this helps.

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166126

Rows.Count in a regular module is the same as ActiveSheet.Rows.Count and is the number of rows in the sheet (populated or not).

So Cells(rows.count, "A") is the last cell in Col A.

someRange.End(xlUp) is the same as selecting someRange and pressing Ctrl+Up. It doesn't necessarily count the number of occupied cells - it just finds the next occupied cell above the starting point (if the starting point is empty). You can experiment by selecting a cell then pressing Ctrl+Up to see what happens.

Upvotes: 0

Related Questions