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

User · Answer

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

User · Answer

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

User · Answer

I ve implemented it like this  Public Function LastRowWithData ByVal strCol As String  ByVal intRow As Integer  As Long     Range strCol  amp  intRow  Select     LastRowWithData  ActiveSheet Cells ActiveSheet Rows Count  strCol  End xlUp  Row End Function

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[excel] Row count where data exists

Examples related to excel

Examples related to vba

Examples related to range