Return the value of the last used row in a column

[flicky_longcartridge]

As the title would suggest I want to return the value of the last used row of data in column 'A', I know that I can use something like

LastRow = ActiveSheet.UsedRange.Rows.Count

But the tricky part is that I want the code to ignore any cell which contains a formula.

Anyone know how to do this?

Thanks!!

 

[flicky_longcartridge]

I guess maybe I could use a while loop to loop backwards until it found the first cell in the column without a formula derived value (i.e. a user inputted value)

But not exactly sure how I would go about phrasing it!

 

[facethegod]

Maybee...

Sub tst()
Dim LastRow As Integer
On Error Resume Next
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
    LastRow = .Areas.Count
    LastRow = .Areas(LastRow).Cells(1, 1).Row + .Areas(LastRow).Rows.Count - 1
End With
On Error GoTo 0
End Sub

HTH

 

[markadon]

You can also use

Range("A65536").End(xlup).Value

 

[flicky_longcartridge]

Hmm that code doesnt appear to work. Returns a value of 0 which would indicate that it is finding a calculated cell! Is there a way I can return the reference of the cell it is finding rather than the value so that I can check exactly where it is stopping?

 

[markadon]

Sure. You can use the Row property as follows:

Range("A65536").End(xlup).Row

 

[mikerickson]

The .End property might reference a cell that contains a formula.
This will return the value last cell in column A that holds a constant

With Range("A:A").SpecialCells(xlCellTypeConstants)
    With .Areas(.Areas.Count)
        With .Item(.Cells.Count)
            MsgBox .Address & " contains " & .Value
        End With
    End With
End With