• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to get the last used row number

vaios

New Member
Hello,
Am doing some data entry every day filling several new rows. How can i get the number of the last filled row and place it in a new sheet ?
Can this be automaticaly change when i add new data ?
 
Yeah sorry Bosco you are right.
I made a small example of the data entry am doing. As i add rows every day and the row number increases. I do need to get the last row number - 1 (the first row is unusable) to use it as a report eg. how many entries do we have so far.
That value i'd like to place in a cell in another sheet.
in the examples case is 9
 

Attachments

  • de.jpg
    de.jpg
    35.9 KB · Views: 31
Hi,

1] You have 3 columns of data with heading, Column A and C are text values, Column B is numeric values.

2] The formula for last row number of Column A and C :

=MATCH("zzzz",A:A)-1

and,

=MATCH("zzzz",C:C)-1

3] The formula for last row number of Column B :

=MATCH(9.99E+307,B:B)-1

Regards
Bosco
 
Thank you for your fast reply.
I only need the number of the last row regardless of the columns and their value types.
 
vaios - test this...
Code:
Function LastRowColumn(sht As Worksheet, RowColumn AsString) AsLong
'PURPOSE: Function To Return the Last Row Or Column Number In the Active Spreadsheet
'INPUT: "R" or "C" to determine which direction to search
  Dim rc AsLong
  Select Case LCase(Left(RowColumn, 1)) 'If they put in 'row' or column instead of 'r' or 'c'.
    Case "c"
      LastRowColumn = sht.Cells.Find("*", LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Case "r"
      LastRowColumn = sht.Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Case Else
      LastRowColumn = 1
  End Select
End Function
from: https://www.thespreadsheetguru.com/...to-find-the-last-row-or-last-column-using-vba
 
Gave it some tries and though but I can't understand it at the moment, will get it more later and let you know.
 
Vaios

A way to know last line used

I hope I have helped

Decio
 

Attachments

  • Exemplo Decio.xlsx
    10.5 KB · Views: 15
Hi, vaios!
Code:
Option Explicit

Public Function lLastRow(Optional pvColumn As Variant) As Long
    ' constants
    Const ksADefault = "A"
    ' declarations
    Dim A As String, I As Long
    ' start
    If Not IsMissing(pvColumn) Then
        A = CStr(pvColumn)
    Else
        A = ksADefault
    End If
    ' process
    I = Range(A & "1").End(xlDown).End(xlDown).End(xlUp).Row
    ' end
    lLastRow = I
End Function

This is the code for the function lLastRow.
Usage:
=lLastRow(), will return last row of column A (default)
=lLastRow("X"), will return last row of column X

Place it in any module (.bas).

Regards!
 
Siga

The formula shows the last line used and not how many lines used

One way to know how many lines were used uses this formula

=COUNTA(E3:E8)

I hope I have helped

Decio
 
Siga

The formula shows the last line used and not how many lines used

Decio

The last line used is 8 in your example. The 9 would be the line to start with probably?
But I am following vaios logic in his example. He shows 10 rows filled in and says that 1st line shouldn't be counted. 10-1=9 (his answer).
Looking at your example, you filled in 8 rows (2 are blank, but should be counted), so the answer is 6.
Yes, COUNTA is perfect for that.
 
Vaios

A way to know last line used

I hope I have helped

Decio

Hi Decio.

The formula in post #.10

=MATCH(1,MMULT(0+(D3:D23=""),1),0)

The above formula return the 1st blank cell row number in the range of D3:D23 only

It does not show the last used cell row number, e. g.

If D3 give blank, D4:D7 have data, the formula will return 1

Regards
Bosco
 
Last edited:
COUNTA ,atleast for my needs right now, works perfectly fine!!! Big thanks to everyone! Great help.
 
Back
Top