Since function nextVisibleRow 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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
'**********************************************************************************************************
' 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