Row count where data exists

Question

I need to count the total number of rows that have data. I want to be able to use this on multiple sheets with different amounts of data rows.

I cannot figure out generic code that will count the number of rows from A1-A100 or A1-A300.

I am trying to use something like this.

i = ActiveWorkbook.Worksheets("Sheet1").Range("A2 , Range("A2").End(xlDown)).Rows.Count

score 20 · Accepted Answer · answered Aug 06 '13 at 19:47

If you need VBA, you could do something quick like this:

Sub Test()
    With ActiveSheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    MsgBox lastRow
    End With
End Sub

This will print the number of the last row with data in it. Obviously don't need MsgBox in there if you're using it for some other purpose, but lastRow will become that value nonetheless.

score 8 · Answer 2 · answered Aug 06 '13 at 19:42

Assuming that your Sheet1 is not necessary active you would need to use this improved code of yours:

i = ActiveWorkbook.Worksheets("Sheet1").Range("A2" , Worksheets("Sheet1").Range("A2").End(xlDown)).Rows.Count

Look into full worksheet reference for second argument for Range(arg1, arg2) which important in this situation.

score 3 · Answer 3 · answered Jan 08 '17 at 23:16

I found this method on http://www.mrexcel.com/

This computes the number of non-blank cells in column A of worksheet named "Data"

With Worksheets("Data")
  Ndt =Application.Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
  debug.print Ndt
End With

The result is printed to the immediate window. You need to subtract 1 (or more) if column A has a header line (or lines) you do not wish to count.

score 2 · Answer 4 · answered Aug 27 '19 at 06:09

2

lastrow = Sheet1.Range("A#").End(xlDown).Row

This is more easy to determine the row count.
Make sure you declare the right variable when it comes to larger rows.
By the way the '#' sign must be a number where you want to start the row count.

answered Aug 27 '19 at 06:09

kyoya007

31
1

score 0 · Answer 5 · answered Aug 06 '13 at 19:25

0

Have you tried this?:

    countif(rangethatyouhave, not(""))

I don't think you need to open the code editor, you can just do it in the spreadsheet itself.

answered Aug 06 '13 at 19:25

Mardin Yadegar

437
4
10

score 0 · Answer 6 · answered Sep 24 '20 at 10:05

I've implemented it like this:

Public Function LastRowWithData(ByVal strCol As String, ByVal intRow As Integer) As Long
    Range(strCol & intRow).Select
    LastRowWithData= ActiveSheet.Cells(ActiveSheet.Rows.Count, strCol).End(xlUp).Row
End Function

score -1 · Answer 7 · edited Jul 10 '16 at 19:23

This works for me. Returns the number that Excel displays in the bottom status line when a pivot column is filtered and I need the count of the visible cells.

Global Const DashBoardSheet = "DashBoard"
Global Const ProfileColRng = "$L:$L"
.
.
.
Sub MySub()
Dim myreccnt as long
.
.
.
myreccnt = GetFilteredPivotRowCount(DashBoardSheet, ProfileColRng)
.
.
.
End Sub

Function GetFilteredPivotRowCount(sheetname As String, cntrange As String) As long

Dim reccnt As Long

reccnt = Sheets(sheetname).Range(cntrange).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants).Count - 1

GetFilteredPivotRowCount = reccnt

End Function

Row count where data exists

7 Answers7

Linked