First non-empty row

Function firstNonEmptyRow returns the index number of the first non-blank row in a given Excel sheet.

Since function firstNonEmptyRow uses some non-built VBA functions, they also must be included in your code for the function to work properly. Otherwise the following error will occur: Compile error: Sub or Function not defined.

Required functions are listed below. You can get to each function's source code by clicking its name.

When adding the functions above to your VBA project, make sure you haven't done it before. If there are two different public functions with the same name in a single VBA project, the following compilation error is thrown:
Compile error: Ambiguous name detected: function_name.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
'**************************************************************************
' Name:       firstNonEmptyRow
' Author:     mielk | 05.04.2012
'
' Comment:    Returns the index number of the first non empty row in a given
'             Excel worksheet.
'
' Parameters:
'   wks       An Excel worksheet, for which the first non-empty row is to
'             be found.
'   ignoreHiddenRows
'             Optional parameter. It determines if the function should
'             ignore hidden rows when searching.
'             By default, this parameter is set to False. If it is omitted,
'             the function searches through all the rows, no matter if they
'             are hidden or not.
'
' Returns:
'   Long      The index number of the first non-empty row in a given Excel
'             worksheet.
'             If there is no non-empty row in the given worksheet 0 is
'             returned.
'**************************************************************************

Public Function firstNonEmptyRow(wks As Worksheet, _
                Optional ignoreHiddenRows As Boolean = False) As Long
    Dim lRow As Long
    Dim lRowStart As Long
    Dim lRowEnd As Long
    Dim lNonBlanks As Long
    Dim rng As Range

    lRowStart = 1
Retry:
    lRowEnd = wks.Cells.rows.count
    lRow = lRowEnd

    Do
        Set rng = wks.Range(wks.Cells(lRowStart, 1), _
                               wks.Cells(lRow, 1)).EntireRow
        lNonBlanks = Application.WorksheetFunction.CountA(rng)

        If lNonBlanks Then
            If lRow = lRowStart Then Exit Do
            lRowEnd = lRow
            lRow = lRowStart + ((lRow - lRowStart - 1) / 2)
        Else
            lRowStart = lRow + 1
            lRow = lRowEnd
            If lRowStart > wks.rows.count Then
                lRow = 0
                Exit Do
            End If
        End If
    Loop


    If lRow Then
        If ignoreHiddenRows And wks.rows(lRow).Hidden Then
            lRowStart = nextVisibleRow(wks, lRow, xlDown)
            If lRowStart <= wks.rows.count Then GoTo Retry
        Else
            firstNonEmptyRow = lRow
        End If
    End If

End Function