Finding row count in vba - method explanation

Question

I have been trying t0 find the row count of a particular column using vba,

I have found that it can be done by using two methods, all three of which involve range ( I pretty much found this by a simple google search and then editing what I found to suit my needs)

However I feel that it would suit me better if I understood what each of them did and how they were different from each other

The two codes I used are as follows :

i = Application.CountA(Range("A:A"))

lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

Could anyone please explain me how these two work and what is the difference between the two? Also, if you know any other way that's efficient, it would be helpful if you do share it here.

http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba — Siddharth Rout, Jan 07 '14 at 03:02
Using `CountA` will give you the total number of occupied cells and not the last row. To find the last row, use the second one or as shown in the link above. — Siddharth Rout, Jan 07 '14 at 03:04

score 3 · Answer 1 · edited Jan 07 '14 at 03:12

3

i = Application.CountA(Range("A:A"))

This returns the count of all the cells in column A that contain a value. They do not have to be in consecutive cells.

lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

This goes to the very last cell at the bottom of column A then does the equivalent of pressing the End key and then the up arrow key. This will return the row index of last cell in the column that contains a value.

edited Jan 07 '14 at 03:12

WGS

13,969
4
48
51

answered Jan 07 '14 at 03:04

aphoria

19,796
7
64
73

`This will select the last cell in the column that contains a value.` + 1 :) However, It will `NOT SELECT` the last cell as you mentioned above :) – Siddharth Rout Jan 07 '14 at 03:06
3

Rather than "select", I'd use "return" though. Both methods above gets and returns a value, rather than move and actively change a selection. ;) – WGS Jan 07 '14 at 03:11
Should’t the first one be `i = Application.WorksheetFunction.CountA(Range("A:A"))`? – Palec Jan 07 '14 at 03:21
When the methods are called without object qualifier, they are run on `Workbook.ActiveSheet`. The dots before the method names are important. – Palec Jan 07 '14 at 03:22

score 2 · Answer 2 · edited May 23 '17 at 12:08

Using Find is superior

Unlike xlUp caters for hidden cells (but not filtered out cells)
Avoids false reporting for blank or the far less likely full column. See here

Find method

Sub GetLastA()
Dim rng1 As Range
Set rng1 = Columns("A:A").Find("*", [a1], xlFormulas)
If Not rng1 Is Nothing Then
  MsgBox "last row is " & rng1.Row
Else
  MsgBox "no used cells found", vbCritical
End If
End Sub

score 0 · Answer 3 · edited Oct 18 '18 at 13:54

0

Sub row_cownt()
Dim R As Double
Dim C As Double

R = Cells.Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

C = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column


MsgBox "Row =" & R
MsgBox "Column =" & C
End Sub

edited Oct 18 '18 at 13:54

AS Mackay

2,831
9
19
25

answered Oct 18 '18 at 13:48

Rameez Khan

1
1

Finding row count in vba - method explanation

3 Answers3