Since function lastNonEmptyColumn 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
146
147
148
149
150
151
152
153
154
155
'**********************************************************************************************************
' 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