[excel] Row count where data exists

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

This question is related to excel vba range

The answer is


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.


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

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.


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

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.


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.


Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?

Examples related to vba

Copy filtered data to another sheet using VBA Better way to find last used row Check if a value is in an array or not with Excel VBA Creating an Array from a Range in VBA Excel: macro to export worksheet as CSV file without leaving my current Excel sheet VBA: Convert Text to Number What's the difference between "end" and "exit sub" in VBA? Rename Excel Sheet with VBA Macro Extract Data from PDF and Add to Worksheet Quicker way to get all unique values of a column in VBA?

Examples related to range

How does String substring work in Swift Creating an Array from a Range in VBA How to create range in Swift? Why is "1000000000000000 in range(1000000000000001)" so fast in Python 3? How to find integer array size in java How does one make random number between range for arc4random_uniform()? Skip over a value in the range function in python Excel Define a range based on a cell value How can I generate a random number in a certain range? Subscript out of range error in this Excel VBA script