asked on

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

Hi,

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.

Thanks

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Steve

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

als315

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
xlf.Quit
Set xlf = Nothing
Set wbk = Nothing
Set wks = Nothing
End Function

Open in new window

sample:

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

Martin Liss

Here's a short article on the subject

als315

@MartinLiss: this link was in my first comment

alam747

ASKER

Thanks a lot, it works