My Blog

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

1

In continuation from here

If you are a frequent user of Excel and do a lot of programming (via VBA or automating Excel from VB.Net) then you know how important finding the last row is. This bit of information is very useful. Be it

1. You are trying to write to the next available row.
2. You are trying to create an Autofilter which covers your complete dynamic range
3. Formatting a dynamic range
4. Finding the offset (1 row) to the last row which has data so you can add new data to the next available row.

etc etc…

Excel easily lets you find the last row, be it the last row of a column or the last row in the worksheet. Below is an example (VBA and VB.Net) on how to find the last row of a column and a sheet.

For testing purpose, let’s say we have an Excel file Sample.xls at C:\. There is data till Row 10 in Col A and there is data till row 15 in Col F (See image below).

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


VBA


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

Option Explicit

Sub GetColA_LastRow()
    Dim ws As Worksheet
    Dim lRow As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
        
    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
    End With
    
    MsgBox "The last row which has data in Col A of Sheet1 is " & lRow
End Sub

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

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

Option Explicit

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

When you run this code, it retrieves the last row 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.

Public Class Form1
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim xlApp As New Excel.Application
        Dim xlWb As Excel.Workbook
        Dim xlsheet As Excel.Worksheet
        Dim lRow 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
                lRow = .Range("A" & .Rows.Count).End(Excel.XlDirection.xlUp).Row
            End With

            MessageBox.Show("The last row in Col A of Sheet1 which has data is " & lRow)

            '~~> 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
End Class

When you run the above code, you get this.

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

Your code should look like this

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim xlApp As New Excel.Application
        Dim xlWb As Excel.Workbook
        Dim xlsheet As Excel.Worksheet
        Dim lRow 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
                    lRow = .Cells.Find(What:="*", _
                                  After:=.Range("A1"), _
                                  LookAt:=Excel.XlLookAt.xlPart, _
                                  LookIn:=Excel.XlFindLookIn.xlFormulas, _
                                  SearchOrder:=Excel.XlSearchOrder.xlByRows, _
                                  SearchDirection:=Excel.XlSearchDirection.xlPrevious, _
                                  MatchCase:=False).Row
                Else
                    lRow = 1
                End If
            End With

            MessageBox.Show("The last row in Sheet1 which has data is " & lRow)

            '~~> 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
End Class

When you run the above code, you get this.

Finding the last row is important when you are working with dynamic range. For example you have a database where you are adding new rows every day and you need to find the next available row.

  1. Advanced Excel training in Gurgaon
    Advanced Excel training in Gurgaon08-01-2013

    Very informative and interesting Blog….

    Thanks for Sharing!!

Add Comment Register



Leave a Reply

Every the things you need regarding assignment help online is right here, simply just use it. . this store hydroponics kit with nutrients