Return value of most recent non-blank value in column

[artz]

Hi,

I have a column of data values separated by rows with blanks. How can I return value of the most recent non-blank value in a column row using VBA for use in a VBA sub?

Thanks,

Art

 

[EuEdu]

Hi, artz,

I'm new here, hope this can help:

Option Explicit


Sub SkipBlank()


Dim to_use As String
Dim cell, data, target As Range


Set target = Range("a1", "a10")
Set data = Range("b1")


For Each cell In target


    If cell.Value <> "" Then
        to_use = cell.Value
        data.Value = to_use
        Set data = data.Offset(1, 0)
    End If
    
    
Next


End Sub

With what you said, this macro do exactly what you want.

 

[artz]

Hi EuEdu,

Thanks for your prompt reply. So, I am starting with the following VBA code:

Sub Compare()
Dim i As Long
For i = 2 To 302
    If Range("B" & i).Value >= Range("F" & i).Value Then
         Range("O" & i) = Range("F" & i).Value
    End If
Next
End Sub

This does not work because the value in column B does not reflect the previous and latest value in column F. Do you see a way to combine your code with my code to accomplish what I was asking for?

Any help is appreciated.

-Art

 

[artz]

Hi EuEdu,

Thanks for your code. It does work as advertised.

Thanks again,

-Art

 

[EuEdu]

Hi EuEdu,

Thanks for your code. It does work as advertised.

Thanks again,

-Art

Hey, Art,

Sorry for days missing, life is getting me hard, haha.

Well, if it helped you made my day!

 

[artz]

Hi EuEdu,

Everybody is busy these days, no problem. I do appreciate the help!

Thanks,

-Art