Since function findHeaderRow 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
'**********************************************************************************************************
' Name: findHeaderRow
' Author: mielk | 2014-12-17
'
' Comment: Function to find header row in the given Excel worksheet.
' Function assumes that the header row is the first row having any value in the last
' non-empty column.
'
' Parameters:
' wks Excel worksheet for which the index of header row is to be returned.
'
'
' Returns:
' Long Index number of a header row in the given Excel workbook.
' The row having any value in the last non-empty column is considered to be the
' header row.
'
' Examples:
' -----------------------------------------------------------------------------------
' * Let's assume there are 10 non-empty columns in the given Excel worksheet.
' In first row there is only date in cell A1, other cells are empty.
' In second row all columns from 1 to 10 are filled with names of column. So, this
' row is the first one having value in last non-empty column (10) and its index (2)
' will be returned as the header row.
'
'
' Exceptions:
' IllegalSheetException Thrown if the worksheet given to the function as the [wks] parameter is
' damaged or has been closed and it is not possible to refer to its rows
' or columns.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2014-12-17 mielk Function created.
'**********************************************************************************************************
Public Function findHeaderRow(wks As Excel.Worksheet) As Long
Const METHOD_NAME As String = "findHeaderRow"
'------------------------------------------------------------------------------------------------------
Dim lastColumn As Long
'------------------------------------------------------------------------------------------------------
'Checks if the given worksheet is valid and can be referred to. -------------------------------------|
'If not, the code jumps to the label IllegalSheetException, where you can define your own '|
'error handling rules for this exception. '|
If Not isSheetValid(wks) Then GoTo IllegalSheetException '|
'----------------------------------------------------------------------------------------------------|
lastColumn = lastNonEmptyColumn(wks)
findHeaderRow = firstNonEmptyRow(wks:=wks, startCol:=lastColumn, endCol:=lastColumn)
'==========================================================================================================
ExitPoint:
Exit Function
'----------------------------------------------------------------------------------------------------------
IllegalSheetException:
'Error handling for the case if the given worksheet has been closed or removed.
GoTo ExitPoint
End Function