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

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

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

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


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Sandeep Sharma" wrote in message m...
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

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

"Ron de Bruin" wrote...
....
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:="*", _
....
"Sandeep Sharma" wrote in message
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

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

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

Hi Harlan

I see now he posted in
microsoft.public.excel.programming,microsoft.publi c.excel.misc,microsoft.public.excel.worksheet.func tions.

I read it in programming and answer there

Thanks for the correction

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Harlan Grove" wrote in message ...
"Ron de Bruin" wrote...
...
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:="*", _
...
"Sandeep Sharma" wrote in message
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

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

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

"Harlan Grove" wrote in message ...
"Ron de Bruin" wrote...
....

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.

....

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

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

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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Sandeep Sharma" wrote in message
om...

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


Thanks,
Sandeep

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

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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Norman Harker" wrote in message ...
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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Sandeep Sharma" wrote in message
om...

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


Thanks,
Sandeep

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

See this website
http://www.contextures.com/xlfaqApp.html#Unused


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ron de Bruin" wrote in message ...
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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Norman Harker" wrote in message ...
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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Sandeep Sharma" wrote in message
om...

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


Thanks,
Sandeep

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

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

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)
...

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)
...

The Set statement should be

Set c = ur.Cells(1, i)

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.

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

Hi Harlan

This are useful functions


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Harlan Grove" wrote in message ...
"Ron de Bruin" wrote...
..
If your usedrange is bigger then your data on the sheet
the formula will not work correct(display 0).
..

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)
..

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)
..

The Set statement should be

Set c = ur.Cells(1, i)

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.