'**********************************************************************************************************
' 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




'**********************************************************************************************************
' Name:                 isSheetValid
' Author:               mielk | 2012-11-16
'
' Comment:              Function to check if the given Excel worksheet is valid and you can refer to its
'                       properties and methods without errors.
'
'                       Using this function is very helpful since it lets you avoid
'                       Run-time error '-2147221080 (800401a8)': Automation error.
'                       This error is generated, when the code tries to refer to a property or a method
'                       of a Sheet type variable, but the sheet pointed by this variable had been deleted
'                       before or the Excel workbook containing this sheet had been closed.
'
' Parameters:
'   wks                 The Excel worksheet to be checked.
'
' Returns:
'   Boolean             True - if the given worksheet is valid and you can refer to it without any errors.
'                       False - if the given worksheet is invalid, that means it is corrupted or had been
'                               closed or deleted before this method has been called and referring to it
'                               will raise an error.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2012-11-16            mielk           Function created.
'**********************************************************************************************************
Public Function isSheetValid(wks As Excel.Worksheet) As Boolean
    Const METHOD_NAME As String = "isSheetValid"
    '------------------------------------------------------------------------------------------------------
    Dim strSheetName As String
    '------------------------------------------------------------------------------------------------------
    
    On Error Resume Next
    strSheetName = wks.name
    
    'Check method is very easy - if the name of the given worksheet has been assigned to the variable
    '[strSheetName], this worksheet is valid and you can refer to it. Otherwise, error would be generated
    'and step [strSheetName = wks.name] would be skipped because of [On Error Resume Next] statement above.
    isSheetValid = VBA.Len(strSheetName)
    
End Function





'**********************************************************************************************************
' Name:                 lastNonEmptyColumn
' Author:               mielk | 2012-04-05
'
' Comment:              Function to return the index number of the last non-empty column in a given
'                       Excel worksheet.
'
' Parameters:
'   wks                 An Excel worksheet for which the last non-empty column is to be found.
'   startRow            Optional parameter of Long type.
'                       * It allows to limit the range being searched. If this argument is specified macro
'                           starts searching from this row instead of the first row in the given worksheet
'                           and ignores all the rows above it.
'                       * If this argument is equal to 0, macro works as if it is not specified at all and
'                           searching starts from the first row of this file.
'   startCol            Optional parameter of Long type.
'                       * It allows to limit the range being searched. If this argument is specified macro
'                           starts searching from this column instead of the first column in the given
'                           worksheet.
'                       * If this argument is equal to 0, macro works as if it is not specified at all and
'                           searching starts from the first column of this file.
'   endRow              Optional parameter of Long type.
'                       * It allows to limit the range being searched. If this argument is specified macro
'                           searches only to this row instead of to the end of the file and ignores all
'                           the cells below this row.
'                       * If this argument is equal to 0, macro works as if it is not specified at all
'                           and searches through all the rows to the end of this file.
'   endCol              Optional parameter of Long type.
'                       * It allows to limit the range being searched. If this argument is specified macro
'                           analyzes range only to this column instead of to the end of this worksheet.
'                       * If this argument is equal to 0, macro works as if it is not specified at all
'                           and searches all the columns to the end of this file.
'   ignoreHiddenCells   Optional parameter of Boolean type.
'                       * It determines if the function should ignore hidden cells when searching.
'                       * By default, this parameter is set to False. If it is omitted, the function
'                           includes are cells, no matter if they are hidden or not.
'
'
' Returns:
'   Long                The index number of the last non-empty column in a given Excel worksheet.
'                       If there is no non-empty column in the given worksheet 0 is returned.
'
'
' 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 -----------------------------------------------------------------------------------------
' 2012-04-05        mielk           Function created.
' 2013-04-19        mielk           Optional argument [endCol] added that allow to search from the
'                                   specified row instead of the end of the given file.
' 2013-04-23        mielk           Optional arguments [startRow] and [endRow] added that allow to
'                                   search the specified range of rows only instead of the whole worksheet.
'**********************************************************************************************************
Public Function lastNonEmptyColumn(wks As Excel.Worksheet, _
                   Optional startRow As Long, Optional startCol As Long, _
                   Optional endRow As Long, Optional endCol As Long, _
                   Optional ignoreHiddenCells As Boolean = False) As Long
    Const METHOD_NAME As String = "lastNonEmptyColumn"
    '------------------------------------------------------------------------------------------------------
    Dim lngCol As Long
    Dim lngColStart As Long
    Dim lngColEnd As Long
    Dim lngNonBlanks As Long
    Dim lngRowStart As Long
    Dim lngRowEnd As Long
    Dim rng As Excel.Range
    '------------------------------------------------------------------------------------------------------


    '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                                            '|
    '----------------------------------------------------------------------------------------------------|

    
    
    'Calculate the actual range to be searched including optional parameters passed to the function. ----|
    If startCol > 0 And startCol <= wks.columns.Count Then lngColStart = startCol Else lngColStart = 1  '|
    If endCol > 0 And endCol <= wks.columns.Count Then lngColEnd = endCol Else _
                                                                        lngColEnd = wks.columns.Count   '|
    If startRow > 0 And startRow <= wks.rows.Count Then lngRowStart = startRow Else lngRowStart = 1     '|
    If endRow > 0 And endRow <= wks.rows.Count Then lngRowEnd = endRow Else lngRowEnd = wks.rows.Count  '|
    '----------------------------------------------------------------------------------------------------|
                         


Retry:
    lngCol = 1
    
    '----------------------------------------------------------------------------------------------------|
    Do                                                                                                  '|
        Set rng = wks.Range(wks.Cells(lngRowStart, lngCol), wks.Cells(lngRowEnd, lngColEnd))            '|
        lngNonBlanks = Excel.Application.WorksheetFunction.CountA(rng)                                  '|
                                                                                                        '|
        '--------------------------------------------------------------------------------------------|  '|
        If lngNonBlanks Then                                                                        '|  '|
                                                                                                    '|  '|
            If lngCol = lngColEnd Then Exit Do                                                      '|  '|
            lngColStart = lngCol                                                                    '|  '|
            lngCol = lngCol + ((lngColEnd - lngCol + 1) / 2)                                        '|  '|
                                                                                                    '|  '|
        Else                                                                                        '|  '|
            lngColEnd = lngCol - 1                                                                  '|  '|
            lngCol = lngColStart                                                                    '|  '|
                                                                                                    '|  '|
            '------------------------------------------------------------------------------------|  '|  '|
            If lngColStart < lngColEnd Then                                                     '|  '|  '|
                lngCol = 0                                                                      '|  '|  '|
                Exit Do                                                                         '|  '|  '|
            End If                                                                              '|  '|  '|
            '------ [If lngColStart < lngColEnd Then] -------------------------------------------|  '|  '|
                                                                                                    '|  '|
                                                                                                    '|  '|
        End If                                                                                      '|  '|
        '---------- [If lngNonBlanks Then] ----------------------------------------------------------|  '|
                                                                                                        '|
    Loop                                                                                                '|
    '----------------------------------------------------------------------------------------------------|



    '----------------------------------------------------------------------------------------------------|
    If lngCol Then                                                                                      '|
                                                                                                        '|
        '--------------------------------------------------------------------------------------------|  '|
        If ignoreHiddenCells And wks.columns(lngCol).Hidden Then                                    '|  '|
            lngColEnd = nextVisibleColumn(wks, lngCol, Excel.xlToLeft)                              '|  '|
            If lngColEnd Then GoTo Retry                                                            '|  '|
        Else                                                                                        '|  '|
            lastNonEmptyColumn = lngCol                                                             '|  '|
        End If                                                                                      '|  '|
        '--------------------------------------------------------------------------------------------|  '|
                                                                                                        '|
    End If                                                                                              '|
    '-------------- [If lngCol Then] --------------------------------------------------------------------|



'==========================================================================================================
ExitPoint:
    Exit Function

'----------------------------------------------------------------------------------------------------------
IllegalSheetException:
    '(...)
    'Put your own error handling here for a case if the given worksheet has been closed or removed.
    
    GoTo ExitPoint


End Function




 
'**********************************************************************************************************
' Name:                 nextVisibleColumn
' Author:               mielk | 2013-03-24
'
' Comment:              Returns the index number of the next visible column to the left or right from the
'                       given column.
'
' Parameters:
'   wks                 Worksheet where the search is processed.
'   initialCol          Initial column, where the search for the next visible column starts.
'   direction           Direction of the search for the next visible column.
'                       * The only available values for this parameter are xlToLeft and xlToRight.
'                       * Technically, the other constant values defined in xlDirection enumeration
'                         (xlUp and xlDown) can also be passed to this function. However, the function will
'                         return 0 in such case, since columns cannot be searched vertically.
'
' Returns:
'   Long                The number index of the next visible column in a specific direction (determined by
'                       the value of direction parameter) from a given initial column.
'
'                       The function returns 0, if there is no visible column to the left or right from a
'                       given column or if the value of direction parameter is other than xlToLeft or
'                       xlToRight.
'
'                       The index number of the very first visible column in a given Excel worksheet can be
'                       obtained by passing parameters initialRow = 0 and direction = xlToRight to this
'                       function.
'
' 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 -----------------------------------------------------------------------------------------
' 2012-04-05        mielk           Function created.
'**********************************************************************************************************
Public Function nextVisibleColumn(wks As Excel.Worksheet, initialCol As Long, _
                                          direction As XlDirection) As Long
    Const METHOD_NAME As String = "nextVisibleColumn"
    '------------------------------------------------------------------------------------------------------
    Dim intOffset As Integer
    '------------------------------------------------------------------------------------------------------
                

    '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                                            '|
    '----------------------------------------------------------------------------------------------------|
                
                
    '----------------------------------------------------------------------------------------------------|
    Select Case direction                                                                               '|
        Case Excel.xlToLeft:    intOffset = -1                                                          '|
        Case Excel.xlToRight:   intOffset = 1                                                           '|
        Case Else                                                                                       '|
            nextVisibleColumn = initialCol                                                              '|
            GoTo ExitPoint                                                                              '|
    End Select                                                                                          '|
    '----------------------------------------------------------------------------------------------------|


    'Iterate through the columns to the left or right from the initial column and check if --------------|
    'they are visible or hidden.                                                                        '|
    nextVisibleColumn = initialCol                                                                      '|
    Do                                                                                                  '|
        nextVisibleColumn = nextVisibleColumn + intOffset                                               '|
        If Not wks.columns(nextVisibleColumn).Hidden Then Exit Do                                       '|
    Loop                                                                                                '|
    '----------------------------------------------------------------------------------------------------|



'==========================================================================================================
ExitPoint:
    Exit Function

'----------------------------------------------------------------------------------------------------------
IllegalSheetException:
    '(...)
    'Put your own error handling here for a case if the given worksheet has been closed or removed.
    
    GoTo ExitPoint

End Function




'**********************************************************************************************************
' Name:                 firstNonEmptyRow
' Author:               mielk | 2012-04-05
'
' Comment:              Function to return the index number of the first non-empty row in the given
'                       Excel worksheet.
'
' Parameters:
'   wks                 An Excel worksheet for which the first non-empty row is to be found.
'   startRow            Optional parameter of Long type.
'                       * It allows to limit the range being searched. If this argument is specified macro
'                           starts searching from this row instead of the first row in the given worksheet
'                           and ignores all the rows above it.
'                       * If this argument is equal to 0, macro works as if it is not specified at all and
'                           searching starts from the first row of this file.
'   startCol            Optional parameter of Long type.
'                       * It allows to limit the range being searched. If this argument is specified macro
'                           starts searching from this column instead of the first column in the given
'                           worksheet.
'                       * If this argument is equal to 0, macro works as if it is not specified at all and
'                           searching starts from the first column of this file.
'   endRow              Optional parameter of Long type.
'                       * It allows to limit the range being searched. If this argument is specified macro
'                           searches only to this row instead of to the end of the file and ignores all
'                           the cells below this row.
'                       * If this argument is equal to 0, macro works as if it is not specified at all
'                           and searches through all the rows to the end of this file.
'   endCol              Optional parameter of Long type.
'                       * It allows to limit the range being searched. If this argument is specified macro
'                           analyzes range only to this column instead of to the end of this worksheet.
'                       * If this argument is equal to 0, macro works as if it is not specified at all
'                           and searches all the columns to the end of this file.
'   ignoreHiddenCells   Optional parameter of Boolean type.
'                       * It determines if the function should ignore hidden cells when searching.
'                       * By default, this parameter is set to False. If it is omitted, the function
'                           includes are cells, no matter if they are hidden or not.
'
' Returns:
'   Long                The index number of the first non-empty row in the given Excel worksheet.
'                       If there is no non-empty rows in the given worksheet 0 is returned.
'
'
' 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 -----------------------------------------------------------------------------------------
' 2013-04-23        mielk           Function created.
'**********************************************************************************************************
Public Function firstNonEmptyRow(wks As Excel.Worksheet, _
                      Optional startRow As Long, Optional startCol As Long, _
                      Optional endRow As Long, Optional endCol As Long, _
                      Optional ignoreHiddenCells As Boolean = False) As Long
    Const METHOD_NAME As String = "firstNonEmptyRow"
    '------------------------------------------------------------------------------------------------------
    Dim lngRow As Long
    Dim lngRowStart As Long
    Dim lngRowEnd As Long
    Dim lngNonBlanks As Long
    Dim lngColStart As Long
    Dim lngColEnd As Long
    Dim rng As Excel.Range
    '------------------------------------------------------------------------------------------------------


    '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                                            '|
    '----------------------------------------------------------------------------------------------------|

    
    'Calculate the actual range to be searched including optional parameters passed to the function. ----|
    If startCol > 0 And startCol <= wks.columns.Count Then lngColStart = startCol Else lngColStart = 1  '|
    If endCol > 0 And endCol <= wks.columns.Count Then lngColEnd = endCol Else _
                                                                        lngColEnd = wks.columns.Count   '|
    If startRow > 0 And startRow <= wks.rows.Count Then lngRowStart = startRow Else lngRowStart = 1     '|
    If endRow > 0 And endRow <= wks.rows.Count Then lngRowEnd = endRow Else lngRowEnd = wks.rows.Count  '|
    '----------------------------------------------------------------------------------------------------|




Retry:

    '----------------------------------------------------------------------------------------------------|
    If endRow > 0 And endRow <= wks.rows.Count Then                                                     '|
        lngRow = endRow                                                                                 '|
    Else                                                                                                '|
        lngRow = wks.rows.Count                                                                         '|
    End If                                                                                              '|
    '----------------------------------------------------------------------------------------------------|
    

    '----------------------------------------------------------------------------------------------------|
    Do                                                                                                  '|
        Set rng = wks.Range(wks.Cells(lngRowStart, lngColStart), wks.Cells(lngRow, lngColEnd))          '|
        lngNonBlanks = Excel.Application.WorksheetFunction.CountA(rng)                                  '|
                                                                                                        '|
                                                                                                        '|
        '--------------------------------------------------------------------------------------------|  '|
        If lngNonBlanks Then                                                                        '|  '|
                                                                                                    '|  '|
            If lngRow = lngRowStart Then Exit Do                                                    '|  '|
            lngRowEnd = lngRow                                                                      '|  '|
            lngRow = lngRowStart + ((lngRow - lngRowStart - 1) / 2)                                 '|  '|
                                                                                                    '|  '|
        Else                                                                                        '|  '|
            lngRowStart = lngRow + 1                                                                '|  '|
            lngRow = lngRowEnd                                                                      '|  '|
                                                                                                    '|  '|
            '------------------------------------------------------------------------------------|  '|  '|
            If lngRowStart > lngRowEnd Then                                                     '|  '|  '|
                lngRow = 0                                                                      '|  '|  '|
                Exit Do                                                                         '|  '|  '|
            End If                                                                              '|  '|  '|
            '------------ [If lngRowStart > lngRowEnd Then] -------------------------------------|  '|  '|
                                                                                                    '|  '|
        End If                                                                                      '|  '|
        '---------------- [If lngNonBlanks Then] ----------------------------------------------------|  '|
                                                                                                        '|
    Loop                                                                                                '|
    '----------------------------------------------------------------------------------------------------|


    '----------------------------------------------------------------------------------------------------|
    If lngRow Then                                                                                      '|
                                                                                                        '|
        '--------------------------------------------------------------------------------------------|  '|
        If ignoreHiddenCells And wks.rows(lngRow).Hidden Then                                       '|  '|
            lngRowStart = nextVisibleRow(wks, lngRow, Excel.xlDown)                                 '|  '|
            If lngRowStart <= wks.rows.Count Then GoTo Retry                                        '|  '|
        Else                                                                                        '|  '|
            firstNonEmptyRow = lngRow                                                               '|  '|
        End If                                                                                      '|  '|
        '---------------- [If ignoreHiddenCells And wks.rows(lngRow).Hidden Then] -------------------|  '|
                                                                                                        '|
    End If                                                                                              '|
    '-------------------- [If lngRow Then] --------------------------------------------------------------|




'==========================================================================================================
ExitPoint:
    Exit Function

'----------------------------------------------------------------------------------------------------------
IllegalSheetException:
    '(...)
    'Put your own error handling here for a case if the given worksheet has been closed or removed.
    
    GoTo ExitPoint


End Function






'**********************************************************************************************************
' Name:                 nextVisibleRow
' Author:               mielk | 2013-03-24
'
' Comment:              Returns the index number of the next visible row up or down from a given row.
'
' Parameters:
'   wks                 Worksheet where the search is processed.
'   initialRow          Initial row, where the search for the next visible row starts.
'   direction           Direction of the search for the next visible row.
'                       * The only available values for this parameter are xlUp and xlDown.
'                       * Technically, the other constant values defined in xlDirection enumeration
'                         (xlToLeft and xlToRight) can also be passed to this function. However, the
'                         function will return 0 in such case, since rows cannot be searched horizontally.
'
' Returns:
'   Long                The number index of the next visible row in a specific direction (determined by
'                       the value of direction parameter) from a given initial row.
'
'                       The function returns 0, if there is no visible row up or down from a given row or
'                       if the value of direction parameter is other than xlUp or xlDown.
'
'                       The index number of the very first visible row in a given Excel worksheet can be
'                       obtained by passing parameters initialRow = 0 and direction = xlDown to this
'                       function.
'
'
' 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 -----------------------------------------------------------------------------------------
' 2012-04-05        mielk           Function created.
'**********************************************************************************************************
Public Function nextVisibleRow(wks As Excel.Worksheet, initialRow As Long, _
                                                                          direction As XlDirection) As Long
    Const METHOD_NAME As String = "nextVisibleRow"
    '------------------------------------------------------------------------------------------------------
    Dim intOffset As Integer
    '------------------------------------------------------------------------------------------------------
                

    '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                                            '|
    '----------------------------------------------------------------------------------------------------|
                
    '----------------------------------------------------------------------------------------------------|
    Select Case direction                                                                               '|
        Case Excel.xlUp:    intOffset = -1                                                              '|
        Case Excel.xlDown:  intOffset = 1                                                               '|
        Case Else                                                                                       '|
            nextVisibleRow = initialRow                                                                 '|
            GoTo ExitPoint                                                                              '|
    End Select                                                                                          '|
    '----------------------------------------------------------------------------------------------------|


    'Iterate through the rows up or down from the initial rows and check if they are visible or hidden. -|
    nextVisibleRow = initialRow                                                                         '|
    Do                                                                                                  '|
        nextVisibleRow = nextVisibleRow + intOffset                                                     '|
        If Not wks.rows(nextVisibleRow).Hidden Then Exit Do                                             '|
    Loop                                                                                                '|
    '----------------------------------------------------------------------------------------------------|



'==========================================================================================================
ExitPoint:
    Exit Function

'----------------------------------------------------------------------------------------------------------
IllegalSheetException:
    '(...)
    'Put your own error handling here for a case if the given worksheet has been closed or removed.
    
    GoTo ExitPoint

End Function