Second Last Used Cell In Column

Raynon · Dec 22nd 2006

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

Robert B · Dec 22nd 2006

Re: Find Value Of Non-blank Cell

Hi
Try this, I have not tested it

Code

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

Raynon · Dec 22nd 2006

Re: Find Value Of Non-blank Cell

Hi Robert,

It's not working. The output is "1" in your code.

Wigi · Dec 22nd 2006

Re: Find Value Of Non-blank Cell

Hi Raynon, Robert

use

Code

Sub test1()
    Dim myValue As String
    With Range("A" & Range("A" & Rows.Count).End(xlUp).Row)
        If .Offset(-1) = "" Then
            myValue = .End(xlUp)
        Else
            myValue = .Offset(-1)
        End If
    End With
    MsgBox myValue
End Sub

Display More

Wigi

Robert B · Dec 22nd 2006

Re: Find Value Of Non-blank Cell

Is this better

Code

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

Wigi · Dec 22nd 2006

Re: Find Value Of Non-blank Cell

Robert

you shoud change

[vba]Offset(1, 0)[/vba]

into

[vba]Offset(-1, 0)[/vba]

Wigi

Robert B · Dec 22nd 2006

Re: Find Value Of Non-blank Cell

Thanks Wigi,

that was careless, I must try harder

Robert

Raynon · Dec 23rd 2006

Re: Second Last Used Cell In Column

Robert and Wigi, Thanks alot! It works now!

Second Last Used Cell In Column

Participate now!

Share