Bolte IT Consulting

Get last column with data in worksheet

If you have to get the last column in an Excel worksheet, the following method can assist since you cannot rely on the UsedRange of Excel.

Why can't you rely on the UsedRange? Read below on how to get the last row in an Excel worksheet.

Determining last row with data in an Excel Worksheet

Source Code

 

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
' @Author - Alexander Bolte
' @ChangeDate - 2015-08-19
' @Description - This approach works only, if no cells are merged.
' If this approach is applied on a worksheet that contains
' merged cells the runtime error number '91' will occur
' since Excel 2000.
'
' Check for merged cells and work around this problem
' using the getDataRange function.
'
' The getDataRange is much slower than the getLastColWithData
' and getLastRowWithData functions but on the other hand it's
' save.
'
' If the given sheet in the given workbook does not conatin any data,
' this function returns -1.
' @Param tmpWs - an object of type Worksheet.
' @Returns - a Long providing the last column holding data in a provided worksheet independent from its UsedRange.
Function funcGetLastColWithData(ByRef tmpWS As Worksheet) As Long
    Dim lastCol As Long
 
On Error GoTo errHandle:
 
    ' ### unmerge cannot be applied on list objects, since merge is not possible on them ###
    If tmpWS.ListObjects.Count = 0 Then
        Call tmpWS.Rows.UnMerge
        Call tmpWS.Columns.UnMerge
    End If
    With tmpWS
        If Application.WorksheetFunction.CountA(.Cells) > 0 Then
            lastCol = .Cells.find(What:="*", After:=.Cells(1), _
                        LookIn:=xlFormulas, LookAt:=xlWhole, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious).Column
        Else
            lastCol = -1
        End If
    End With
    
errHandle:
    
    funcGetLastColWithData = lastCol
End Function
Hits: 4095