1

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
braX
  • 11,506
  • 5
  • 20
  • 33
Namita Ojha
  • 11
  • 1
  • This post has several answers which may illustrate the difference between `Count` and `End(xlUp)` ... https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba – user10186832 Mar 09 '23 at 10:15

2 Answers2

0

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.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
0

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.

Alok
  • 368
  • 3
  • 13