PDA

View Full Version : VBA Function - Last Row



joshua1990
08-31-2017, 07:49 AM
Hey Guys!:)

I'm currently trying to implement/create a function (VBA based) for the last row in the respective column.
I'm working a lot with dynamic tables, so it could rate high the performance and usibility.


For Example:

=SUM(C2:LastRow(3))

I know there are some combined excel-funtions that defines the respective last row but I want it a bit more professional.

Do anyone have a suggestion for this objective?
Is there anything to consider about? Maybe some weak points with working with something like that?


Best regards
Joshua

Paul_Hossler
08-31-2017, 08:05 AM
Not 100% sure I'm understanding, but maybe something these as a starting point?

The Yellow is the data, the Orange is RangeToEnd(), and the Green are LastRowAddr

20213







Option Explicit

Function LastRowAddr(C As Long, Optional RowAbsolute As Boolean = True, Optional ColumnAbsolute As Boolean = True) As String
LastRowAddr = Cells(Rows.Count, C).End(xlUp).Address(RowAbsolute, ColumnAbsolute)
End Function

Function RangeToEnd(R As Range) As Range
Set RangeToEnd = Range(R, Cells(Rows.Count, R.Column).End(xlUp))
End Function

mdmackillop
08-31-2017, 08:42 AM
or maybe =SUMDOWN(C2)

Function SUMDOWN(r As Range)
SUMDOWN = WorksheetFunction.Sum(Range(r, Cells(Rows.Count, r.Column).End(xlUp)))
End Function

joshua1990
08-31-2017, 10:32 AM
Thanks guys for your posts.

I'm sorry that I was to imprecise.

The objectiv is to get the address of the last row for the respective column. That respective column would be defined with the index number, inside the brackets of the function.
For example the function for colum C:
=LastRow(3)
> C15

I'm working with a lot of functions, including different operations according to the scope of a respective column.


Do you need a further or more detailed explanation?

Are there any weak points for that?
Maybe I'm on a wrong way.


Best regards!

Paul_Hossler
08-31-2017, 10:48 AM
Thanks guys for your posts.

I'm sorry that I was to imprecise.

The objectiv is to get the address of the last row for the respective column. That respective column would be defined with the index number, inside the brackets of the function.
For example the function for colum C:
=LastRow(3)
> C15


And LastRowAddr() in post #2 doesn't do that?

Kenneth Hobs
08-31-2017, 10:55 AM
Depends on what you want I guess. Here is one way to get the string and range values. It is similar to what the other two guys showed you.

Some of it is redundant but shows a concept.

'=SUM(INDIRECT("C2:"&slastrow("C")))
Function sLastRow(col As Variant, Optional os As Long) As String
Application.Volatile
If Not IsNumeric(col) Then col = Columns(col).Column()
sLastRow = Cells(Cells.Rows.Count, col).End(xlUp).Offset(os).Address
End Function

'=SUM(C2:rlastrow("C"))
Function rLastRow(col As Variant, Optional os As Long) As Range
Application.Volatile
If Not IsNumeric(col) Then col = Columns(col).Column()
Set rLastRow = Cells(Cells.Rows.Count, col).End(xlUp).Offset(os)
End Function

joshua1990
08-31-2017, 10:57 AM
Hey Paul!

I'm sorry, I have some problems with the buildung of the function in excel.

Of cource, your function delivers a relative address of the respective last row.
But how can I put or include this defined function into an regular function?
Are there any symbols or operators necessary.

I got a #Value with the following constructions

=SUM(A2:LastRowAddr(1))
=SUM(A2:(LastRowAddr(1)))
=SUM(A2:"LastRowAddr(1)")
=SUM(A2:&LastRowAddr(1)&)

Thanks Kenneth!

It works with the following formulation

=SUM(INDIRECT("C2:"&LastRowAddr(1)))

Paul_Hossler
08-31-2017, 11:05 AM
Hey Paul!

I'm sorry, I have some problems with the buildung of the function in excel.

Of cource, your function delivers a relative address of the respective last row.
But how can I put or include this defined function into an regular function?
Are there any symbols or operators necessary.

I got a #Value with the following constructions
=SUM(A2:LastRowAddr(1))
=SUM(A2:(LastRowAddr(1)))
=SUM(A2:"LastRowAddr(1)")
=SUM(A2:&LastRowAddr(1)&)






The objectiv is to get the address of the last row for the respective column. That respective column would be defined with the index number, inside the brackets of the function.
For example the function for colum C:

Sorry, when you said "address" I thought you mean the .Address as a String

LastRowAddr returns a string, so =SUM("A2:"&LastRowAddr(1)) would work


But if you want a Range (like C15) then




Function LastRow(C As Long) As Range
Set LastRow = Cells(Rows.Count, C).End(xlUp)
End Function



so =SUM(A2:LastRow(1)) would work

20214

SamT
08-31-2017, 06:24 PM
standard module code

Option Explicit

Public Function LastRowAddress(ColNum As Long) As String
Dim LastCell As Range
Set LastCell = Application.Caller.Parent.Cells(Rows.Count, ColNum).End(xlUp)
LastRowAddress = LastCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
End Function



Returns an address like A2 with no $ signs. works on all worksheets

snb
08-31-2017, 11:09 PM
Eine dynamische Tabelle.
Spalte 1 : Spalte Name: "aa1"

Letzte Zelle in Spalte1:
=INDEX(A:A;ROWS(Table1[[#All];[aa1]]);1)

Aber das braucht man nicht zum summieren von Spalte C; dann reicht


=SUM(Table1[[#All];[aa3]])

joshua1990
09-01-2017, 12:41 AM
Thanks guys for all your posts!

We have now the following solutions:


Function LastRowAddr(C As Long, Optional RowAbsolute As Boolean = True, Optional ColumnAbsolute As Boolean = True) As String
LastRowAddr = Cells(Rows.Count, C).End(xlUp).Address(RowAbsolute, ColumnAbsolute)
End Function

This works great. It delivers a relative address of the last row.


Function SUMDOWN(r As Range) SUMDOWN = WorksheetFunction.Sum(Range(r, Cells(Rows.Count, r.Column).End(xlUp))) End Function

I dont think this will solve the problem. The objective is to create a function for the last row for a definite column.



Function rLastRow(col As Variant, Optional os As Long) As Range Application.Volatile
If Not IsNumeric(col) Then col = Columns(col).Column()
Set rLastRow = Cells(Cells.Rows.Count, col).End(xlUp).Offset(os) End Function

Is this working with the index of the column and the letter (A)?
Or whats the big difference in this kind?



Function LastRow(C As Long) As Range
Set LastRow = Cells(Rows.Count, C).End(xlUp)
End Function

I'm sorry. I not have know, that I searched the range and not exactly the address.



Option Explicit

Public Function LastRowAddress(ColNum As Long) As String
Dim LastCell As Range
Set LastCell = Application.Caller.Parent.Cells(Rows.Count, ColNum).End(xlUp)
LastRowAddress = LastCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
End Function

Probably this is the best approach, isn't it?
Are there some weak points?

@snb:

The objective was/ is a VBA function. I know, there are different ways with combined excel-functions to solve this problem.



@all:
Which approach would you choose and why?

snb
09-01-2017, 02:13 AM
No, it isn't


Function F_snb()
F_snb = ActiveSheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp).Address
End Function
or

Function F_snb(y) ' specify the column
F_snb = ActiveSheet.Cells(Rows.Count, y).End(xlUp).Address
End Function
or

Function F_snb(c00,y) ' specify the sheet and the column
F_snb = Sheets(c00).Cells(Rows.Count, y).End(xlUp).Address
End Function

Paul_Hossler
09-01-2017, 05:56 AM
C is the column number, not Column "C"




Function LastRow(C As Long) As Range
Set LastRow = Cells(Rows.Count, C).End(xlUp)
End Function



Look at the screen shot in #8 and how it's used in the formula

joshua1990
09-01-2017, 06:34 AM
C is the column number, not Column "C"




Function LastRow(C As Long) As Range
Set LastRow = Cells(Rows.Count, C).End(xlUp)
End Function



Look at the screen shot in #8 and how it's used in the formula


Yeah, I know (:

The post referred to the following code:


Function SUMDOWN(r As Range)
SUMDOWN = WorksheetFunction.Sum(Range(r, Cells(Rows.Count, r.Column).End(xlUp)))
End Function
The variable r is declared as a range-object. So it doesn't work with numbers, but yours approach.

SamT
09-01-2017, 06:45 AM
Probably this is the best approach, isn't it?
Are there some weak points?
I don't like use numbers to refer to columns. By changing the name and Type of ColNum to Col and Variant, one can use either
=LastRowAddress(3)
or
=LastRowAddress("C")


Public Function LastRowAddress(Col) As String
Dim LastCell As Range
Set LastCell = Application.Caller.Parent.Cells(Rows.Count, Col).End(xlUp)
LastRowAddress = LastCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
End Function

By adding a line, one can even use Ranges by simply clicking a cell when using fx to insert the function

Public Function LastRowAddress(Col) As String
Dim LastCell As Range
If TypeName(Col) = "Range" Then Col = Col.Column
Set LastCell = Application.Caller.Parent.Cells(Rows.Count, Col).End(xlUp)
LastRowAddress = LastCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
End Function

With that, the formula options are:
=LastRowAddress(3)
or
=LastRowAddress("C")
Or
=LastRowAddress (C2)
OR
=LastRowAddress(C:C)
OR
even other Functions that return a Range reference

mdmackillop
09-01-2017, 07:45 AM
Are there some weak points?
Ensure that the function is Volatile (mine is not). Changing a value within the range does not force a recalculation.

Paul_Hossler
09-01-2017, 08:35 AM
Expanding on SamT's and mac's ideas to increase flexibility ...







=SUM($A$1:LastRow(1))


=SUM($A$1:LastRow("a"))


=SUM($A$1:LastRow($A$7))









Option Explicit
Public Function LastRow(Col As Variant) As Variant

Application.Volatile

With Application.Caller.Parent
If TypeName(Col) = "Range" Then
Set LastRow = .Cells(.Rows.Count, Col.Cells(1, 1).Column).End(xlUp)

ElseIf IsNumeric(Col) Then
Set LastRow = .Cells(.Rows.Count, Col).End(xlUp)

ElseIf VarType(Col) = vbString Then
Set LastRow = .Cells(.Rows.Count, Col).End(xlUp)

Else
LastRow = CVErr(xlErrNA)
End If
End With
End Function

SamT
09-01-2017, 01:09 PM
Ensure that the function is Volatile (mine is not). Changing a value within the range does not force a recalculation.

Public Function LastRowAddress(Col) As String
Dim LastCell As Range
Application.Volatile

If TypeName(Col) = "Range" Then Col = Col.Column

Set LastCell = Application.Caller.Parent.Cells(Rows.Count, Col).End(xlUp)
LastRowAddress = LastCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
End Function

joshua1990
09-02-2017, 04:42 AM
Thanks guys for the adjustments!

Maybe there are a futher more point to figure out.
A classic excel-function rearrange the respective column or/ and cell by pulling the formula along column or row, of course only if the address/ range is relative declared.
Is this dynamic adoption also possible to include?

SamT
09-02-2017, 06:29 AM
To learn Excel VBA: Record Macro...

Open a new Excel.
In Cell A1 place a 1 (ein )
In Cell B1 place "=A1"
Select A1:B5
Excel Menu >> Tools >> Macro >> Record New Macro >> Store Macro in this workbook >> OK
Press Ctrl+D
Excel Menu >> Tools >> Macro >> Stop Recording


In C1 place "=NOW()"
Cell Format C1 >> Custom >> "MMM" (MMM)
Record New Macro
Select C1:C5
Excel Menu >> Edit >> Fill >> Series >> Date Unit >> Month >> OK
Stop Recording

Open VBA Editor (VBIDE) >> Modules >> Module1

Note difference when Selecting Cells before recording and after recording.

Place Cursor in "FillDown" in one Macro >> Press F1 for Help. In addition, look at all topics in "See Also" in FillDown Help

See "DataSeries" in Help




Typical Recorded Macro:

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 9/2/2017 by SamT
'

'
Range("C1:C5").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _
xlMonth, Step:=1, Trend:=False
End Sub

Note in above macro,
Range("C1:C5").Select
Selection.
Macros cannot read minds, you should delete all such ".Select>Selection.", Leaving only one dot after deletion.