Find Last Column
Dim BlankCellsObjectVariable As Range
On Error Resume Next
Set BlankCellsObjectVariable = RangeObjectWithBlankCells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not BlankCellsObjectVariable Is Nothing Then
StatementsIfBlankCells
End IfFind Last Column
01
02
03
04
05
06
07
08
09
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
Function FindLastColumn(MyWorksheetName As String) As Long
'Source: https://powerspreadsheets.com/
'For further information: https://powerspreadsheets.com/excel-vba-find/
'This UDF:
'(1) Accepts 1 argument: MyWorksheetName
'(2) Tests whether the worksheet named MyWorksheetName is empty
'(3) If the worksheet named MyWorksheetName is empty, returns 0 as the number of the last column with data in the worksheet
'(4) If the worksheet named MyWorksheetName is not empty:
'(1) Finds the last column with data in the worksheet by searching for the last cell with any character sequence
'(2) Returns the number of the last column with data in the worksheet
'Declare object variable to hold/represent all cells in the worksheet named MyWorksheetName
Dim MyRange As Range
'Identify all cells in the worksheet named MyWorksheetName
Set MyRange = ThisWorkbook.Worksheets(MyWorksheetName).Cells
'Test if MyRange is empty
If Application.CountA(MyRange) = 0 Then
'If MyRange is empty, assign 0 to FindLastColumn
FindLastColumn = 0
Else
'If MyRange isn't empty, find the last cell with any character sequence by:
'(1) Searching for the previous match;
'(2) Across columns
FindLastColumn = MyRange.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End If
End FunctionCopyright © 2021 Codeinu
Forgot your account's password or having trouble logging into your Account? Don't worry, we'll help you to get back your account. Enter your email address and we'll send you a recovery link to reset your password. If you are experiencing problems resetting your password contact us
