last row of a column

[hawley]

I am trying to find the last row in a certain column. I know how to it for a sheet. See below
ActiveSheet.UsedRange
ActiveCell.SpecialCell(xlLastCell).Select
glngLastRow = ActiveCell.Row
gintLastCol = ActiveCell.Column

I thought to find the last row for a column I could go.
Columns(intCol).UsedRange
ActiveCell.SpecialCell(xlLastCell).Select
glngLastRow = ActiveCell.Row
gintLastCol = ActiveCell.Column

But this does not work because I get an error at "Columns(intCol).UsedRange" when I run the code.

Any ideas. I have one, finding the blank row of the column I want but is there another way.

[Ivan F Moala]

On 2002-05-23 08:24, hawley wrote:


I am trying to find the last row in a certain column. I know how to it for a sheet. See below
ActiveSheet.UsedRange
ActiveCell.SpecialCell(xlLastCell).Select
glngLastRow = ActiveCell.Row
gintLastCol = ActiveCell.Column

I thought to find the last row for a column I could go.
Columns(intCol).UsedRange
ActiveCell.SpecialCell(xlLastCell).Select
glngLastRow = ActiveCell.Row
gintLastCol = ActiveCell.Column

But this does not work because I get an error at "Columns(intCol).UsedRange" when I run the code.

Any ideas. I have one, finding the blank row of the column I want but is there another way.

Heres one way to do this:

Sub LastRowInColumn()

'// I F M

Dim rLastRow As Range

Dim intCol As Integer

'// Where intCol is the column to search

intCol = 2



Set rLastRow = Columns(intCol).Find(What:="*", SearchDirection:=xlPrevious, _

    SearchOrder:=xlByRows)



If rLastRow Is Nothing Then

    Set rLastRow = Columns(intCol).End(xlDown)

    MsgBox rLastRow.Address

Else

    MsgBox rLastRow.Address

End If



End Sub

_________________
Kind Regards,
Ivan F Moala From the City of Sails


[ This Message was edited by: Ivan F Moala on 2002-05-23 09:32 ]

[dk]

Hello,

You could use this function:-

Function LastRow(ColumnNumber As Long)
Dim rngeCol As Range
On Error Resume Next

If ColumnNumber < 1 Or ColumnNumber > 256 Then
    LastRow = "Invalid column number"
    Exit Function
End If

Set rngeCol = ActiveSheet.Columns(ColumnNumber)

LastRow = rngeCol.Find(What:="*", SearchDirection:=xlPrevious).Row

End Function

An example of using the function from another procedure might be:-

Sub TestIt()
MsgBox "The last used cell in column A is in row " & LastRow(1)
End Sub

HTH,
Dan