CODEWITHSUNDEEP
vbaexcelcompare
EfhK
EfhK

Reputation: 123

VBA Excel search column for last changing value

I've got a column: U. This column has values from U10 till U500.

What I need to get is the last changing value of the column and if it doesn't change then a text "False" or something and if the last changing value is an empty cell, then ignore that..

Column U  
11  
11   
5  
11  
11  
21

For example here the result should be 21.

I've tried comparing two rows and with conditional formatting but with such a big range doing all this for each row is a bit too much.

Does anybody know a good way to do this?

Upvotes: 1

Views: 502

Answers (3)

FreakTester
FreakTester

Reputation: 57

Try this Macro. First run the AnalyseBefore sub and when you want to check if the value has changed run the AfterAnalyse sub. Incase you want the range to be dynamic use the code that I have commented and include iCount in your Range calculation

Sub AnalyseBefore()
Dim iCount
Range("U10").Select
iOvalue = Range("U500").Value
'iCount = Selection.Rows.Count
Range("Z1").Value = iOvalue    
End Sub

Sub AnalyseAfter()
Dim iCount
Range("U10").Select
iNValue = Range("U500").Value
Range("Z2").Value = iNValue
iOvalue = Range("Z1").Value
If (iOvalue = iNValue) Then
Range("U500").Value = "FALSE"
End If    
End Sub

Upvotes: 0

user2648008
user2648008

Reputation: 152

i am not sure of the how you want the output.

IF(AND(RC[-1]<>R[-1]C[-1],ROW(RC[-1])>500,R[-1]C[-1]<>""),RC[-1],"")

try this formula in cells V10:V500

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57693

Something like that should do it ...

Sub test()
    Dim LastRow As Long, i As Long

    With Worksheets("Sheet1") 'your sheet name
        LastRow = .Cells(.Rows.Count, "U").End(xlUp).Row 'find last used row in column U

        For i = LastRow To 2 Step -1 'loop from last row to row 2 backwards (row 1 can not be compared with row before)
            If .Cells(i, "U").Value <> .Cells(i - 1, "U").Value Then 'compare row i with row before. If it changes then ...
                MsgBox "Last row is: " & .Cells(i, "U").Address & vbCrLf & _
                    "Value is: " & .Cells(i, "U").Value
                Exit For 'stop if last changing row is found
            End If
        Next i
    End With
End Sub

It loops from last used row in column U to the first row and checks if the current row is different from the row before. If so it stops.

Upvotes: 2

Related Questions