The Excel method uses the Excel COUNTIF function, combined with asterisk (*) as the criteria to count the number of cells that contain text in a specified range. Both of the VBA methods make use of the CountIf Worksheet Function to count the number of cells that contain text in a specified range.
If there is more than one range and criteria, you can use COUNTIFS function. It works same as COUNTIF, but is used with multiple criteria. The syntax of the COUNTIFS is: =COUNTIF(range 1, criteria1, range 2, criteria 2.. )
A number, expression, cell reference, or text string that determines which cells will be counted. For example, you can use a number like 32, a comparison like ">32", a cell like B4, or a word like "apples". COUNTIF uses only a single criteria. Use COUNTIFS if you want to use multiple criteria.
WorksheetFunction MethodWorksheetFunction is a method of Application object. It allows you access to many (not all) of the standard Excel worksheet functions. Generally, you won't gain access to any worksheet functions that have a corresponding VBA version.
To count non-blank cells with the COUNTIF function, you can use a formula like this:
- =COUNTIF(range,"<>")
- =COUNTIFS(rng1,">100",rng2,"<>")
- =SUMPRODUCT(--(LEN(A1:A100)>0))
- =COUNTBLANK(B4:B9)
Count cells that are an exact matchIn Excel, you can use the COUNTIF function to count cells that meet a specific criterion. For example, you can use this formula to count the orders that were placed by the customer, BigMart. The first argument, $B$2:$B$14, is the range that contains the cells to be counted.
VBA XlUP is a snippet that is used to find out the last row of the given data set in an excel table. So this snippet works as similar to the keyboard shortcut we use in excel worksheet which is CTRL + Down Arrow. This is also known as the range end property method.
From Active Cell to Last Entry
- To select the last entry in a column, simply add the following code line: Range("A5").End(xlDown).Select.
- To select the range from cell A5 to the last entry in the column, add the following code line:
- To select the range from the Active Cell to the last entry in the column, simply replace Range("A5") with ActiveCell.
In
VBA, the
UsedRange property represents the
range in a worksheet that has data in it.
Write a Code with UsedRange
- First, you need to specify the worksheet.
- Then enter a dot (.) and enter “UsedRange”.
- After that, that use the property or method that you want to use.
- In the end, run the code.
Count Cells with Numbers -- COUNT
- Enter the sample data on your worksheet.
- In cell A7, enter an COUNT formula, to count the numbers in column A: =COUNT(A1:A5)
- Press the Enter key, to complete the formula.
- The result will be 3, the number of cells that contain numbers. Cell A1 isn't counted, because it contains text.
In the first syntax “Do Until” loop checks the condition first and gets the condition result is TRUE or FALSE. If the condition is FALSE, it will execute the code and perform a specified task, and if the condition is TRUE, then it will exit the loop.
VBA Activate Worksheet Method- Instructions
- Open an Excel Worksheet.
- Press Alt+F11 :This will Open the VBA Editor.
- Insert a Module from Insert Menu.
- Copy the above code for activating worksheet and Paste in the code window(VBA Editor)
- Save the file as macro enabled Worksheet.
Types of Data Types in VBA
- Byte: A byte comes with a small capacity, and it can hold values ranging from 0 to 255.
- Integer: An integer is a beta version of the byte data type, and it can hold values ranging from -32768 to 32768.
if you want to convert column number to letter, you can use the ADDRESS function to get the absolute reference of one excel cell that contains that column number, such as, you can get the address of Cell B1, then you can use the SUBSTITUTE function to replace row number with empty string, so that you can get the column
Range.To find the last used row in a column, this technique starts at the last cell in the column and goes up (xlUp) until it finds the first non-blank cell. The Rows. Count statement returns a count of all the rows in the worksheet.
