last row in column

[art]

Hello:

Does anyone know of a vba code that give you the last row in a column used,
but the last cell could have a formula? that is is the column A:A has a
formula from A1:A200, but only A1:A35 have actual values in it. Is there a
way to get the last cell in the column that has a value in it?

Thanks

Art

 

[Gary Keramidas]

give this a try


Sub test()
Dim lastrow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, _
"A").End(xlUp).SpecialCells(xlCellTypeConstants).Row
Debug.Print lastrow

End Sub

 

[art]

no, it gives me a value of 1 when there are 5 cells full.

 

[JMay]

Sub Foo()
Dim lrow As Long
lrow = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & lrow).Select
Do Until ActiveCell <> ""
ActiveCell.Offset(-1).Select
Loop
End Sub

 

[JLGWhiz]

This is a little bit cumbersom but it does the job.

Sub test()
Dim lastrow As Long, i As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, _
"A").End(xlUp).Row
If Cells(lastrow, 1).Value > "" Then
MsgBox "Last visible value is cell " & Cells(lastrow, 1).Address
Exit Sub
Else
For i = lastrow To 2 Step -1
If Cells(i, 1) > "" Then
MsgBox "Last visible value is cell " & Cells(i, 1).Address
Exit For
End If
Next
End If
End Sub

 

[Mike]

This should do what you looking for.

Function ISFORMULA(cel As Range) As Boolean
ISFORMULA = cel.HasFormula
End Function

Sub noFormulaInCell()
Const whatColumn As String = "A"
Dim lrow As Long
lrow = Range(whatColumn & Rows.Count).End(xlUp).Row
Range(whatColumn & lrow).Activate
Do Until ISFORMULA(ActiveCell) = False And ActiveCell <> ""
ActiveCell.Offset(-1).Activate
Loop
Debug.Print ActiveCell.Address
End Sub

 

[Gary Keramidas]

how about this?

Sub test()
Dim lastrow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, _
"A").End(xlUp).SpecialCells(xlCellTypeFormulas).Offset(-1).Row
Debug.Print lastrow

End Sub