Answers for "Find Last Column"

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 If

Find 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 Function

Find Last Column

01Function 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 Function
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 Function