Get number of rows filled in particular column -VBA

Question

Right now I can get the number of rows filled in with values in VBA using:

Rows(Rows.Count).End(xlUp).Row

However this line only gets me the number of rows in the first column. What would I do to get the same but for any given column?

Thanks.

EDIT: answer was simply passing the column as argument of Rows()

`Cells(Rows.Count, colNumOrLetterHere).End(xlUp).Row` – Tim Williams Aug 02 '16 at 02:08 — Tim Williams, Aug 02 '16 at 02:08
Thanks Tim! Accepted answer – Rackham Le Rouge Aug 02 '16 at 02:16 — Rackham Le Rouge, Aug 02 '16 at 02:16

score 2 · Answer 1 · answered Aug 02 '16 at 05:06

The question is how to get the number of filled rows in a particular column. That is not the same thing as getting last used row number in a column.

Here is how to count how many non-blank cells in a column

WorksheetFunction.CountA(Columns(2))

There are many ways to references ranges of cells. Here are some examples:

Sub Examples()

    Debug.Print "The last used row in the second column"
    Debug.Print Columns(2).Rows(Rows.Count).End(xlUp).row
    Debug.Print
    Debug.Print "The last used row in the second column"
    Debug.Print Columns("B").Rows(Rows.Count).End(xlUp).row
    Debug.Print
    Debug.Print "The last used row in the second column using Find"
    Debug.Print Columns("B").Find(What:="*", SearchDirection:=xlPrevious).row
    Debug.Print
    Debug.Print "The last used row on the ActiveSheet using Find"
    Debug.Print Cells.Find(What:="*", SearchDirection:=xlPrevious).row
    Debug.Print
    Debug.Print "The last used row in the second column"
    Debug.Print Cells(Rows.Count, 2).End(xlUp).row
    Debug.Print
    Debug.Print "The last used row in the second column"
    Debug.Print Cells(Rows.Count, "B").End(xlUp).row
    Debug.Print
    Debug.Print "The last used row in the second column"
    Debug.Print Range("B" & Rows.Count).End(xlUp).row
    Debug.Print
    Debug.Print "The last used row on the ActiveSheet, this is not limited to Column A"
    Debug.Print Range("A1").SpecialCells(xlCellTypeLastCell).row

    Debug.Print
    Debug.Print "The number of non-blank cells in Column B"
    Debug.Print WorksheetFunction.CountA(Columns(2))

End Sub

score 0 · Answer 2 · answered Aug 02 '16 at 03:12

0

Debug.Print Range("A1").SpecialCells(xlCellTypeLastCell).Row

Above line says no of rows used in current sheet.

answered Aug 02 '16 at 03:12

Sathish Kothandam

1,530
3
16
34

Get number of rows filled in particular column -VBA

2 Answers2