checking for EOF in excel
 

Here are also 2 very nice functions:
Function LASTINROW(rng As Range)
Set LastCell = rng.Parent.Cells(rng.Row, Columns.Count).End(xlToLeft)
LASTINROW = LastCell.Value
If IsEmpty(LastCell) Then LASTINROW = ""
If rng.Parent.Cells(rng.Row, Columns.Count) < "" Then _
LASTINROW = rng.Parent.Cells(rng.Row, Columns.Count)
End Function

Function LASTINCOLUMN(rng As Range)
Set LastCell = rng.Parent.Cells(Rows.Count, rng.Column) _
.End(xlUp)
LASTINCOLUMN = LastCell.Value
If IsEmpty(LastCell) Then LASTINCOLUMN = ""
If rng.Parent.Cells(Rows.Count, rng.Column) < "" Then _
LASTINCOLUMN = rng.Parent.Cells(Rows.Count, rng.Column)
End Function

"Diana" wrote in message
...
I have a table in excel and I need to write a VB
procedure to check which row is the end one. Any
suggestions. I appreciate your help.
Diana

checking for EOF in excel
 

Thank you for writing the formulas but unfortunately
neither one of them is of any help to me. I would like a
formula that would give me the number of the last cell
that is populated in a range. So If my populated range is
A1:D34, I need to know that 34 is the last row...and not
what is in row 34 as your formula returns..

Thank you
Diana
-----Original Message-----
Here are also 2 very nice functions:
Function LASTINROW(rng As Range)
Set LastCell = rng.Parent.Cells(rng.Row,
Columns.Count).End(xlToLeft)
LASTINROW = LastCell.Value
If IsEmpty(LastCell) Then LASTINROW = ""
If rng.Parent.Cells(rng.Row, Columns.Count) < ""
Then _
LASTINROW = rng.Parent.Cells(rng.Row,
Columns.Count)
End Function

Function LASTINCOLUMN(rng As Range)
Set LastCell = rng.Parent.Cells(Rows.Count,
rng.Column) _
.End(xlUp)
LASTINCOLUMN = LastCell.Value
If IsEmpty(LastCell) Then LASTINCOLUMN = ""
If rng.Parent.Cells(Rows.Count, rng.Column) < ""
Then _
LASTINCOLUMN = rng.Parent.Cells(Rows.Count,
rng.Column)
End Function

"Diana" wrote in message
...
I have a table in excel and I need to write a VB
procedure to check which row is the end one. Any
suggestions. I appreciate your help.
Diana


.

checking for EOF in excel
 

Diana,

This was shown earlier in the forum

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



you use it like this for example



MsgBox LastRow(ActiveSheet)



Cells(LastRow(ActiveSheet), 1).Select



--
sb
"Diana" wrote in message
...
Thank you for writing the formulas but unfortunately
neither one of them is of any help to me. I would like a
formula that would give me the number of the last cell
that is populated in a range. So If my populated range is
A1:D34, I need to know that 34 is the last row...and not
what is in row 34 as your formula returns..

Thank you
Diana
-----Original Message-----
Here are also 2 very nice functions:
Function LASTINROW(rng As Range)
Set LastCell = rng.Parent.Cells(rng.Row,
Columns.Count).End(xlToLeft)
LASTINROW = LastCell.Value
If IsEmpty(LastCell) Then LASTINROW = ""
If rng.Parent.Cells(rng.Row, Columns.Count) < ""
Then _
LASTINROW = rng.Parent.Cells(rng.Row,
Columns.Count)
End Function

Function LASTINCOLUMN(rng As Range)
Set LastCell = rng.Parent.Cells(Rows.Count,
rng.Column) _
.End(xlUp)
LASTINCOLUMN = LastCell.Value
If IsEmpty(LastCell) Then LASTINCOLUMN = ""
If rng.Parent.Cells(Rows.Count, rng.Column) < ""
Then _
LASTINCOLUMN = rng.Parent.Cells(Rows.Count,
rng.Column)
End Function

"Diana" wrote in message
...
I have a table in excel and I need to write a VB
procedure to check which row is the end one. Any
suggestions. I appreciate your help.
Diana


.