• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 706
  • Last Modified:

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
0
alam747
Asked:
alam747
2 Solutions
 
als315Commented:
Public Function LastRow() As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
End Function

Here you can find some other methods:
http://www.mrexcel.com/td0058.html
0
 
SteveCommented:
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

0
 
als315Commented:
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")
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Martin LissRetired ProgrammerCommented:
Here's a short article on the subject
0
 
als315Commented:
@MartinLiss: this link was in my first comment
0
 
alam747Author Commented:
Thanks a lot, it works
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now