Q) Last row/column ------ excel formula query

  • Thread starter Thread starter Sandeep Sharma
  • Start date Start date
S

Sandeep Sharma

Kindly suggest what is the best way to reference the following in excel formulas:
+) last row of a worksheet that has some data
+) last column of a worksheet that has some data

Regards,
Sandeep
 
Hi Sandeep

In VBA the best thing is to use this functions
You can use this in your code then for example
Lr = LastRow(Sheets("Sheet2"))


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function
 
Ron de Bruin said:
In VBA the best thing is to use this functions
You can use this in your code then for example
Lr = LastRow(Sheets("Sheet2"))

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _ ....
Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _ ....
Click to expand...

Ron's suggestion only works in VBA when his functions are called from VBA
procedures (Subs). As there's no way to pass worksheet objects from
worksheet cells to VBA udfs, as well as the fact that the .Find *method* of
the Range class always fails when called from UDFs (at least through
XL2000), they won't work as udfs.

Since the OP states that these would be used 'in excel formulas', it's not
unreasonable to suppose that means in cell formulas. To do that, a different
approach is needed.


Function lr(Optional r As Range) As Variant
Dim ur As Range, c As Range, i As Long, n As Long

'there arguments for & against volatility - optional to uncomment
'Application.Volatile

If r Is Nothing Then Set r = Application.Caller
If Not TypeOf r Is Range Then Set r = ActiveCell

Set ur = r.Parent.UsedRange
n = ur.Rows.Count

For i = n To 1 Step -1
Set c = ur.Cells(n, 1)
If Not IsEmpty(c.Value) Then Exit For
If Not IsEmpty(c.End(xlToRight).Value) Then Exit For
Next i

lr = ur.Row + i - 1
End Function


Function lc(Optional r As Range) As Variant
Dim ur As Range, c As Range, i As Long, n As Long

'there arguments for & against volatility - optional to uncomment
'Application.Volatile

If r Is Nothing Then Set r = Application.Caller
If Not TypeOf r Is Range Then Set r = ActiveCell

Set ur = r.Parent.UsedRange
n = ur.Columns.Count

For i = n To 1 Step -1
Set c = ur.Cells(1, n)
If Not IsEmpty(c.Value) Then Exit For
If Not IsEmpty(c.End(xlDown).Value) Then Exit For
Next i

lc = ur.Column + i - 1
End Function
 
Hi Harlan

I see now he posted in
microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions.

I read it in programming and answer there

Thanks for the correction
 
Harlan Grove said:
Since the OP states that these would be used 'in excel formulas', it's not
unreasonable to suppose that means in cell formulas. To do that, a different
approach is needed.
Click to expand...
....

Thanks to all of you for your tips. I am somewhat embarrassed to ask
the follow up question:

Q) How are user-defined functions "installed" within Excel so that
they may be referenced in cell formulas?


Thanks,
Sandeep
 
Hi Sandeep!

You can just put the functions in a module in the workbook that will
call them

You can put them in a module in the Personal.xls file

You can put them in an Addin that you load each time that you open
Excel.



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Sandeep

If your usedrange is bigger then your data on the sheet
the formula will not work correct(display 0).

Use CTRL-End to see your Usedrange

Delete the rows/ columns with no data in the usedrange
 
...
...
If your usedrange is bigger then your data on the sheet
the formula will not work correct(display 0).
Click to expand...
...

That's just due to bugs in my lr and lc functions.
Function lr(Optional r As Range) As Variant ...
For i = n To 1 Step -1
Set c = ur.Cells(n, 1)
Click to expand...
...

The Set statement should be

Set c = ur.Cells(i, 1)

Function lc(Optional r As Range) As Variant ...
For i = n To 1 Step -1
Set c = ur.Cells(1, i)
Click to expand...
...

The Set statement should be

Set c = ur.Cells(1, i)
 
Back
Top