Since function removeEmptyRows 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
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
'**********************************************************************************************************
' Name: removeEmptyRows
' Author: mielk | 2014-09-20
'
' Description: Function to remove all the empty rows from the given 2D array.
'
' Parameters:
' arr Array to be cleared from empty rows.
' It must have two dimensions. If the given array has more or less dimensions, or
' if it is not an array at all, exception will be thrown.
'
'
' Returns:
' Variant() The source 2D array without empty rows.
'
'
' Exceptions:
' NoArrayException Thrown if parameter [arr] is not an array.
' NotDefinedArrayException Thrown if the given array has not been defined yet.
' TooManyDimensionsException Thrown if the given array has more than 2 dimensions.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2014-09-20 mielk Function created.
'**********************************************************************************************************
Public Function removeEmptyRows(arr As Variant) As Variant
Const METHOD_NAME As String = "removeEmptyRows"
'------------------------------------------------------------------------------------------------------
Dim results() As Variant
Dim col As Long
Dim row As Long
Dim resultRow As Long
Dim isEmpty As Boolean
'------------------------------------------------------------------------------------------------------
'Check if the given parameter [arr] is the proper array, that can be processed by this function. ----|
'If it is not an array, code is moved to the label NotArrayException. '|
'If it is an array, but has not been initialized yet, code jumps to NotDefinedArrayException label. '|
'If it is an array, but has less or more than two dimensions, code is moved to the label '|
'TooManyDimensionsException. '|
If Not VBA.IsArray(arr) Then GoTo NotArrayException '|
If Not isDefinedArray(arr) Then GoTo NotDefinedArrayException '|
If countDimensions(arr) <> 2 Then GoTo TooManyDimensionsException '|
'----------------------------------------------------------------------------------------------------|
'Resize the final array to the same size as the original array. It will be trimmed at the end -------|
'of this function. '|
ReDim results(LBound(arr, 1) To UBound(arr, 1), LBound(arr, 2) To UBound(arr, 2)) '|
resultRow = LBound(arr, 2) - 1 '|
'----------------------------------------------------------------------------------------------------|
'Iterate through all the rows from the source array. ------------------------------------------------|
For row = LBound(arr, 2) To UBound(arr, 2) '|
'|
'Check if current row has any non-empty cell. -----------------------------------------------| '|
isEmpty = True '| '|
'| '|
'Iterate through all the cells in the current row. --------------------------------------| '| '|
For col = LBound(arr, 1) To UBound(arr, 1) '| '| '|
'| '| '|
'If there is at least one non-empty cell, this row is not empty and there is ----| '| '| '|
'no point in checking rest of cells in this row. '| '| '| '|
If isNonEmptyString(arr(col, row)) Then '| '| '| '|
isEmpty = False '| '| '| '|
Exit For '| '| '| '|
End If '| '| '| '|
'----------- [If isNonEmptyString(arr(col, row)) Then] --------------------------| '| '| '|
'| '| '|
Next col '| '| '|
'--------------- [For col = LBound(arr, 1) To UBound(arr, 1)] ---------------------------| '| '|
'| '|
'--------------------------------------------------------------------------------------------| '|
'|
'|
'If this row is not marked as empty, add it to the final array. -----------------------------| '|
If Not isEmpty Then '| '|
'| '|
resultRow = resultRow + 1 '| '|
'| '|
'Iterate through all the columns in the current row and add their values to the -----| '| '|
'final array. '| '| '|
For col = LBound(arr, 1) To UBound(arr, 1) '| '| '|
'| '| '|
'Before adding value to the array, it has to be checked if the value is -----| '| '| '|
'an object or a primitive value, because there is a difference in '| '| '| '|
'syntex when appending objects and non-objects. '| '| '| '|
If VBA.IsObject(arr(col, row)) Then '| '| '| '|
Set results(col, resultRow) = arr(col, row) '| '| '| '|
Else '| '| '| '|
results(col, resultRow) = arr(col, row) '| '| '| '|
End If '| '| '| '|
'------- [If VBA.IsObject(value) Then] --------------------------------------| '| '| '|
'| '| '|
Next col '| '| '|
'----------- [For col = LBound(arr, 1) To UBound(arr, 1)] ---------------------------| '| '|
'| '|
'| '|
End If '| '|
'--------------- [If Not isEmpty Then] ------------------------------------------------------| '|
'|
'|
Next row '|
'----------------------------------------------------------------------------------------------------|
'If there are any items in the final array, trim this array to remove empty rows. -------------------|
If resultRow Then '|
ReDim Preserve results(LBound(results, 1) To UBound(results, 1), _
LBound(results, 2) To resultRow) '|
End If '|
'----------------------------------------------------------------------------------------------------|
'==========================================================================================================
ExitPoint:
'Assign final array to the result of this function.
removeEmptyRows = results
Exit Function
'----------------------------------------------------------------------------------------------------------
NotArrayException:
'Error handling for the case if the given parameter [arr] is not an array.
GoTo ExitPoint
NotDefinedArrayException:
'Error handling for the case if the given parameter [arr] is an array but it has not been
'initialized yet.
GoTo ExitPoint
TooManyDimensionsException:
'Error-handling for case if the given parameter is an array but it has more than 2 dimensions.
GoTo ExitPoint
End Function