LAst Row Number

[Monika]

I am finding the lastcell of a sheet through:
tcrLastCell = Cells(Rows.Count, "A").End(xlUp).Row
I forming a range using this last cell somewhere in my
code for doing a vlookup:
c2 = Cells(tcrLastCell, rng6.Column).Address 'ENDING RANGE

This is using the 1st column. Somehow my end-user had to
insert a column "A" in the same sheet. This had some
information in the beginning rows. Now my tcrLastCell
returns 5 and my code fails. Working on excel is so
volatile. The code fails so promptly...is there a smart
way to handle such a case.

many thanks

 

[Jim Cone]

Monika,

The following function can be called like this:
Dim LastRow as Long
LastRow = GetBottomRow(ActiveSheet)

'======================================
' GetBottomRow() Function
' Returns the number of the last worksheet row with data.
' If the sheet is blank it returns 0.
'======================================
Function GetBottomRow(ByRef TheSheet as Worksheet) As Long
On Error GoTo NoRow
If TheSheet.FilterMode Then TheSheet.ShowAllData
GetBottomRow = TheSheet.Cells.Find(what:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Exit Function
NoRow:
GetBottomRow = 0
End Function
'--------------------------------------

Regards,
Jim Cone
San Francisco, CA

 

[mudraker]

Jim

Very nice code for getting the last row



Is there a similar easy way to get the last column number that does no
use range("a1").End(xlToRight).colum

 

[Guest]

thanks a lot Jim...

Seems cool..let em try it!

-----Original Message-----
Monika,

The following function can be called like this:
Dim LastRow as Long
LastRow = GetBottomRow(ActiveSheet)

'======================================
' GetBottomRow() Function
' Returns the number of the last worksheet row with data.
' If the sheet is blank it returns 0.
'======================================
Function GetBottomRow(ByRef TheSheet as Worksheet) As Long
On Error GoTo NoRow
If TheSheet.FilterMode Then TheSheet.ShowAllData
GetBottomRow = TheSheet.Cells.Find(what:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Exit Function
NoRow:
GetBottomRow = 0
End Function
'--------------------------------------

Regards,
Jim Cone
San Francisco, CA

"Monika" <[email protected]> wrote in

message news:[email protected]...

 

[Jim Cone]

mudraker,

LastColumn = TheSheet.Cells.Find(what:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

Regards,
Jim Cone
San Francisco, CA