In this article I will explain the function Get_Count(). Get_Count() is a function which I have written myself. The main purpose of this function is to return the number of rows or columns of data starting from a specific cell. It is very useful when working with data that can dynamically increase or decrease. You can download the files related to this article here. The function code can be seen below:

Jump To:

• Input Parameters
• Example 1
• Example 2

'Retrieves the amount of data at a specific column or row
'intRow: Row to start searching for data
'intColumn: Column to start searching for data
'wrksheet: worksheet to start searching for data
'flagCountRows: Determines whether to search for rows or columns
Public Function Get_Count(ByVal intRow As Integer, ByVal intColumn As Integer, ByRef wrkSheet As Worksheet, _
ByVal flagCountRows As Boolean) As Integer
Dim i As Integer
Dim flag As Boolean

i = 1
flag = True
While flag = True
    If flagCountRows = True Then
        If wrkSheet.Cells(i + intRow - 1, intColumn) <> "" Then
        i = i + 1
        Else
        flag = False
        End If
    Else
        If wrkSheet.Cells(intRow, intColumn + i - 1) <> "" Then
        i = i + 1
        Else
        flag = False
        End If
    End If
Wend

Get_Count = i - 1
End Function


Input Parameters:

intRow: The index of the first row with data in it.

intColumn: The index of the first column with data in it.

wrkSheet: The sheet which contains the data.

flagCountRows: A boolean value which determines whether to search for the number of columns or rows.

Output: The number of rows or columns of data.

Example 1:

Assume that we have the following data in our sheet. We are looking for the number of rows with data in it. In other words we are looking for the index of the first empty cell:

It can be seen that the data starts from row 2 column 1. Assume that these numbers are written in sheet1. Since we are looking for the number of rows, the flagCountRows parameter is set to true:

Sub Example1()
Dim intCountRows As Integer
intCountRows = Get_Count(2, 1, Sheet1, True)
MsgBox (intCountRows)
End Sub


Result:

Example 2:

In this example we are looking for the number of columns in the data below, in sheet2:

It can be seen that the data starts from row 3 and column 2. Also since we are looking for the number of columns, flagCountRows will be set to false:

Sub Example2()
Dim intCountRows As Integer
intCountRows = Get_Count(3, 2, Sheet2, False)
MsgBox (intCountRows)
End Sub


Result:

You can download the files related to this article here.

If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website  www.software-solutions-online.com