Find the last specific value in a column

[Nelson78]

Good morning.

In my work the column D is populated by a word, for example "Europe", a lot of times.
I need a vba code in order to find - and select - the last cell of that column populated by "Europe".

My attempt is just a manual operation recorded by the macro recorder, now I need a help for bigger files.

Thank's.

Sub find_last()
    Columns("D:D").Select
    Cells.Find(What:="europe", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Cells.FindNext(After:=ActiveCell).Activate
End Sub

 

[Fluff]

Hi
How about

Sub lastValue()

    Dim Rng As Range

    Set Rng = Range("D" & Rows.Count).End(xlUp).Offset(1)
    Columns("D").Find("Europe", After:=Rng, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, _
         SearchFormat:=False).Select
        

End Sub

 

[BrainDeficiency]

You can create an Excel formula by placing the following code in a new Module:

Public Function findlast(ByVal colRange As Range, ByVal FindWord As String)
Dim LastRow As Long

LastRow = Cells(Rows.Count, colRange.Column).End(xlUp).Row 'finds the last row in the user-specified column
For i = LastRow To 1 Step -1
    If Cells(i, colRange.Column) = FindWord Then 'loops from the last row in the column to the first to check if it matches user-specified word
        
        findlast = i
        Exit Function
        
    End If
 
Next i

findlast = "Word not found"

End Function

So now that you've done that, if you want to find the last entry in column D, which says Europe, you'd have to type in the following formula anywhere on your spreadsheet: =findlast(D:D,"Europe") . This is obviously customisable.

 

[Nelson78]

The tests done until now are positive in both cases.

Thank you for the solutions.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Nelson78]

Now, I'm struggling with something similar.

Autofilters are applied in my sheet. For the column D, I've filtered the empty values.
Now, with autofilters applied, I need to select the first empty cell.

My attempt is not successful: the selection, infact, is always on cell D2, regardless of its value.

    ActiveSheet.Range("$D$1:$D$10000").AutoFilter Field:=1, Criteria1:="="
    Range("D1").Offset(1, 0).Select

 

[Nelson78]

Perhaps this could be one of the right solutions.

ActiveSheet.Range("$A$1:$E$10000").AutoFilter Field:=4, Criteria1:="="
Columns("D:D").Select
Range("D2:D10000").SpecialCells(xlCellTypeVisible)(1).Select

 

[Nelson78]

Better.

ActiveSheet.Range("$A$1:$E$10000").AutoFilter Field:=4, Criteria1:="="
Range("D2:D10000").SpecialCells(xlCellTypeVisible)(1).Select