How Mnay Records Can Vba Read From Excel

When we use VBA to automate Excel operations on data or tables, 1 of the most common things nosotros want to do is to detect the number of rows of information or the number of records, in a worksheet.

In this commodity, nosotros'll go through a few key approaches to count the number of rows of data in Excel ranges.

Contents

• Count Rows of Data in a Range
  • The Range contains continuous data
  • Some rows in the range are empty
  • Approach 1 – just bank check 1 column to determine used rows
  • Approach two – check all columns to determine used rows
• Count Rows with Specific Discussion
• Decision

Count Rows of Data in a Range

The Range contains continuous data

In this example, we have a listing of monthly expense data as shown in the picture below. The list in continuous with no "broken rows" in betwixt (i.e. no rows within the range are bare). It is easy to find the number of rows in a listing like this.

The macro "countDataRows1" beneath uses range.Rows.Count to discover the number of rows of data in the current selection. To utilize the macro, we first select the list of information, and run the macro. The macro likewise returns the respond in a message box.

Sub countDataRows1() Dim tmp As Long tmp = Selection.Rows.Count MsgBox tmp & " rows of information in the option" End Sub        

However, yous may notice information technology inconvenient to accept to manually select the entire list before running the macro. We tin can enhance the macro to have it automatically notice the "CurrentRegion" of the table range. (Meet line 3 of macro "countDataRows2" below) You'll only have to select whatsoever cell within the listing before running the macro.

Sub countDataRows2() Dim tmp Every bit Long tmp = Choice.CurrentRegion.Rows.Count MsgBox tmp & " rows of data in the choice" End Sub        

Some rows in the range are empty

Unlike databases tables (such as Admission), Excel tables are not e'er continuous. We ofttimes work in Excel worksheets with "gratis-style" layouts and our tables were designed with breaks (empty rows) to make them easier to read. However, this causes an additional claiming when we try to discover the actual number of populated rows in a worksheet with VBA.

Permit's say nosotros have an income statement equally shown below. There are some empty rows (e.chiliad. rows vi, 25, and 28) which separate the key sections of the financial statement. Nosotros desire to use VBA to find:

1. The full number of rows of the income argument (i.e. the height of the statement)
2. The number of rows that are used/filled with data

Some bad news hither is that the method of "CurrentRegion" nosotros used in the previous instance (macro "countDataRows2") won't work anymore in this instance. To illustrate the issue, if we select cell A2 manually, and then press the keyboard shortcut Ctrl+Shift+8 to select the CurrentRegion, just the first block of data from rows 1 to five will be selected (because row half-dozen is empty). Therefore we demand an alternative VBA approach to find the entire range of our income statement.

Nosotros tin apply the "UsedRange" holding of the ActiveSheet object to detect the range of the income statement without being affected by the empty rows, then count the number of rows. The macro "countDataRows3" below does the job in one step in line 3.

Sub countDataRows3() Dim tmp As Long tmp = ActiveSheet.UsedRange.Rows.Count MsgBox "Total number of rows of the income statement is " & tmp Terminate Sub        

The side by side step is to find the number of used rows with data in this income statement. I'm going to demonstrate two approaches below.

Approach i – only cheque one column to decide used rows

Assuming that the cells in Column are ever populated for used rows, we can apply the COUNTA() worksheet function to count the number of used cells in column A of the UsedRange of the worksheet.

Sub countDataRows4() Dim tmp Equally Long Dim tmp2 Every bit Long With ActiveSheet.UsedRange     'number of rows of range     tmp = .Rows.Count     'number of used rows with data     tmp2 = Application.CountA(.Columns(1)) End With MsgBox "Total number of rows of the income statement is " & tmp & Chr(10) & "Number of used rows is " & tmp2 Finish Sub        

In the in a higher place macro "countDataRows4", in line 8, the Excel congenital-in worksheet function CountA has been called in VBA. It is very important to note how it has been called.  You can always telephone call an Excel congenital-in worksheet role using the syntax: "Application.<function_name>" (and so the input parameters).

After running the macro, a bulletin box returns both answers for full number of rows and the number of used rows. (The message box shown below is from the Mac version of Excel)

Approach 2 – check all columns to determine used rows

Even so, in the event we take to cheque through all columns to ensure the unabridged row is empty, we can use a For-Next loop together with the COUNTA worksheet part

The idea is to (ane) find the UsedRange, (ii) loop through every row of the UsedRange, and (3) utilize the COUNTA worksheet function to check whether any one of the cells in each row is not-empty.

Sub countDataRowswithBreaks() Dim counter As Long Dim r As Range With ActiveSheet.UsedRange     'loop through every row in the usedrange     For Each r In .Rows         'cheque if a row contain any used prison cell         If Application.CountA(r) > 0 Then             'counts the number of rows with used cells             counter = counter + i         End If     Next End With MsgBox "Number of used rows is " & counter Cease Sub        

If you lot're interested in techniques to delete empty rows in Word instead of Excel, have a look at this commodity.

Count Rows with Specific Discussion

How about if we want to count the number of rows which contain the word "Expense"? We can conform the previous macro and use the "CountIf" worksheet office (instead of "CountA").

In the macro "countDataRowswithWord" below, line 7, the CountIf part is used to count the number of cells in each row which contains the discussion "Expense". Nosotros utilize the wildcard "*" here, so that the function counts any cell with role of the content which contains "Expense", for instance, "Other Expense 1", "Total Expenses".

Sub countDataRowswithWord() Dim counter As Long Dim r As Range With ActiveSheet.UsedRange     'loop through every row in the usedrange     For Each r In .Rows         'check if a row has whatsoever cell with fractional match         If Awarding.CountIf(r, "*Expense*") > 0 Then 		'counts the number of rows which match criteria             counter = counter + 1         Stop If     Next End With MsgBox "Number of rows is with content match: " & counter End Sub        

Determination

We have gone fully through some key techniques to find the number of rows in a range. In addition to Range.Rows.Count, y'all tin use "CurrentRegion" or "UsedRange" to quickly find out the area of a block of data. Also, y'all can use a For-Next loop to go through every row in a range, together with Excel built-in functions such as "COUNTA()" or "COUNTIF()" to perform more detailed check of specific criteria.

reaganwheive.blogspot.com

Source: https://software-solutions-online.com/excel-vba-count-rows-with-data/

Share this post