My Blog

Find Last Column in an Excel Sheet–VBA/VB.Net

1

In continuation from here

In my last post, I showed you how to find the last row. In this post we will find the last column.

Below is an example (VBA and VB.Net) on how to find the last column of a row which has data or the last column in a sheet which has data.

For testing purpose, let’s say we have an Excel file Sample.xlsx at C:\. (See image below).

Now let’s try and find the last column in VBA and VB.Net


VBA


Let’s try and find the last column of Row 1 first. Open the excel file c:\Sample.xlsx and paste the below code in a module. When you run this code

Option Explicit

Sub GetRow1_LastCol()
    Dim ws As Worksheet
    Dim lCol As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
        
    With ws
        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
    
    MsgBox "The last column which has data in Row 1 of Sheet1 is " & lCol
End Sub

It will retrieve the last column in row 1 and display it in a message box as shown below

Now let’s say we do not know the last column in the sheet which has data. Paste this code in a module and then run it.

Option Explicit

Sub GetLastColumn()
    Dim ws As Worksheet
    Dim lCol As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
        
    With ws
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lCol = .Cells.Find(What:="*", _
                   After:=.Range("A1"), _
                   Lookat:=xlPart, _
                   LookIn:=xlFormulas, _
                   SearchOrder:=xlByColumns, _
                   SearchDirection:=xlPrevious, _
                   MatchCase:=False).Column
        Else
            lCol = 1
        End If
    End With
    
    MsgBox "The last column in Sheet1 which has data is " & lCol
End Sub

When you run this code, it retrieves the last column in the sheet and then displays it in a message box as shown below

Now let’s try the same in VB.net


VB.NET


Create a form and put a button on the form. Set the reference to Microsoft Excel Library. If you do not know how to do that then see this.

Your form should look like this

Now let’s try and find the last row in column A.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim xlApp As New Excel.Application
        Dim xlWb As Excel.Workbook
        Dim xlsheet As Excel.Worksheet
        Dim lCol As Long = 0

        With xlApp
            .Visible = True

            '~~> Open workbook
            xlWb = .Workbooks.Open("c:\Sample.xlsx")

            '~~> Set it to the relevant sheet
            xlsheet = xlWb.Sheets("Sheet1")

            With xlsheet
                lCol = .Cells(1, .Columns.Count).End(Excel.XlDirection.xlToLeft).Column
            End With

            MessageBox.Show("The last column which has data in Row 1 of Sheet1 is " & lCol)

            '~~> Close workbook and quit Excel
            xlWb.Close(False)
            xlApp.Quit()

            '~~> Clean Up
            releaseObject(xlsheet)
            releaseObject(xlWb)
            releaseObject(xlApp)

        End With
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

When you run the above code, you get this.

Similarly, let’s try and find the last column in the worksheet

Your code should look like this

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim xlApp As New Excel.Application
        Dim xlWb As Excel.Workbook
        Dim xlsheet As Excel.Worksheet
        Dim lCol As Long = 0

        With xlApp
            .Visible = True

            '~~> Open workbook
            xlWb = .Workbooks.Open("c:\Sample.xlsx")

            '~~> Set it to the relevant sheet
            xlsheet = xlWb.Sheets("Sheet1")

            With xlsheet
                '~~> Check if there is any data in the sheet
                If xlApp.WorksheetFunction.CountA(.Cells) <> 0 Then
                    lCol = .Cells.Find(What:="*", _
                                  After:=.Range("A1"), _
                                  LookAt:=Excel.XlLookAt.xlPart, _
                                  LookIn:=Excel.XlFindLookIn.xlFormulas, _
                                  SearchOrder:=Excel.XlSearchOrder.xlByColumns, _
                                  SearchDirection:=Excel.XlSearchDirection.xlPrevious, _
                                  MatchCase:=False).Column
                Else
                    lCol = 1
                End If
            End With

            MessageBox.Show("The last column in Sheet1 which has data is " & lCol)

            '~~> Close workbook and quit Excel
            xlWb.Close(False)
            xlApp.Quit()

            '~~> Clean Up
            releaseObject(xlsheet)
            releaseObject(xlWb)
            releaseObject(xlApp)

        End With
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

When you run the above code, you get this.

  1. Transformer
    Transformer06-28-2013

    cool :)