Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spell Checking with checking cell notes | Excel Discussion (Misc queries) | |||
Excel keeps checking for a floppy... | Excel Discussion (Misc queries) | |||
Checking excel for errors/inconsistencies | Excel Worksheet Functions | |||
checking for EOF in excel | Excel Programming | |||
VBA Excel Checking whether an XL file is already open elsewhere | Excel Programming |