VBA code to get the total line count of an excel sheet with data

Posted on 2012-09-09
Last Modified: 2012-09-11

I need to write data from the last row of the existing sheet.
How to get the last line of an excel sheet with data then I can use as Line counter to write data after the last updated raw.

Please advise if there easy way to get the last raw(line number) with data of an excel sheet.
if any function which return the total of record of a excel sheet. which can be use as a Line number for update data from next blank raw of the excel sheet.

Question by:alam747
    LVL 39

    Accepted Solution

    Public Function LastRow() As Long
    LastRow = ActiveSheet.UsedRange.Rows.Count
    End Function

    Here you can find some other methods:
    LVL 24

    Assisted Solution

    UsedRange can be a tad unreliable... the most reliable method is:

    LastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

    Open in new window

    Though to find the last row in an Excel Row ( in this case A)

    LastRow = Range("A" & Rows.count).End(xlUp).Row

    Open in new window

    LVL 39

    Expert Comment

    I think it is used for adding data from Access and UsedRange is enough for this task.
    Function for Access:
    Public Function excel_sheet_last_row(xls As String, sheet As String) As Long
    Dim xlf As Object, wbk As Object, wks As Object
    Set xlf = CreateObject("Excel.Application")
    Set wbk = xlf.Workbooks.Open(xls)
    Set wks = wbk.Sheets(sheet)
    excel_sheet_last_row = wks.UsedRange.Rows.Count
    Set xlf = Nothing
    Set wbk = Nothing
    Set wks = Nothing
    End Function

    Open in new window


    Dim i As Long
    i = excel_sheet_last_row("c:\tmp\book1.xlsx", "Sheet1")
    LVL 44

    Expert Comment

    by:Martin Liss
    Here's a short article on the subject
    LVL 39

    Expert Comment

    @MartinLiss: this link was in my first comment

    Author Closing Comment

    Thanks a lot, it works

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
    Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now