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()
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 Debug.Print Range("A1").SpecialCells(xlCellTypeLastCell).Row Above line says no of rows used in current sheet.
Cells(Rows.Count, colNumOrLetterHere).End(xlUp).Row