Hi All,
I need to find a value of a non-blank cell. For example(see the attachment), i need a code which can extract the value of the 2nd last non-blank row of 1st column which is "9" in this case. Can anybody help
Hi All,
I need to find a value of a non-blank cell. For example(see the attachment), i need a code which can extract the value of the 2nd last non-blank row of 1st column which is "9" in this case. Can anybody help
Re: Find Value Of Non-blank Cell
Hi
Try this, I have not tested it
Sub test1()
Dim myValue As String, R As Long
R = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & R).Select
If ActiveCell.Offset(1, 0) = "" Then
Selection.End(xlUp).Select
myValue = ActiveCell
Else
myValue = ActiveCell.Offset(1, 0)
End If
End Sub
Display More
See [luc]*[/luc] and [v]*[/v]
HTH
Robert
Re: Find Value Of Non-blank Cell
Hi Robert,
It's not working. The output is "1" in your code.
Re: Find Value Of Non-blank Cell
Is this better
Sub test1()
Dim myValue As Integer, R As Long
R = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & R).Activate
myValue = ActiveCell
If ActiveCell.Offset(1, 0) = "" Then
Selection.End(xlUp).Select
myValue = ActiveCell
Else
myValue = ActiveCell.Offset(-1, 0)
End If
End Sub
Display More
Robert
Re: Find Value Of Non-blank Cell
Robert
you shoud change
[vba]Offset(1, 0)[/vba]
into
[vba]Offset(-1, 0)[/vba]
Wigi
Re: Find Value Of Non-blank Cell
Thanks Wigi,
that was careless, I must try harder
Robert
Re: Second Last Used Cell In Column
Robert and Wigi, Thanks alot! It works now!
Don’t have an account yet? Register yourself now and be a part of our community!