CODEWITHSUNDEEP
excelvbarange
Patrick Wilson
Patrick Wilson

Reputation: 195

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

Upvotes: 13

Views: 258716

Answers (7)

Tonky75
Tonky75

Reputation: 142

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

Upvotes: 0

kyoya007
kyoya007

Reputation: 31

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.

Upvotes: 2

Markm0705
Markm0705

Reputation: 1440

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.

Upvotes: 3

pnacamuli
pnacamuli

Reputation: 1

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

Upvotes: -1

Joe Laviano
Joe Laviano

Reputation: 1048

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.

Upvotes: 21

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

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.

Upvotes: 8

Mardin Yadegar
Mardin Yadegar

Reputation: 437

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.

Upvotes: 0

Related Questions