Go to last row where column A has a defined value

[Malen Steptoe]

I have a user form with a text field where the date is entered. I want to find the last instance of that date, insert a row beneath it and add the data in the user form into that row.

How do I go to the last row with the same value as that in the text field? I know how to do it for the last row with data in it but not specific data.

Cheers in advance

 

[gallen]

Hello and welcome.

You would use the Range.Find method ensuring the direction is set to xlPrevious.

So for example this code looks for the last instance of 01/12/19 in column A:

Sub LastDate()
    Dim d As Date, rFind As Range
    
    
    d = "01/12/19"
    
    Set rFind = Range("A:A").Find(What:=d, SearchDirection:=xlPrevious)
    
    If Not rFind Is Nothing Then
        'Display the address of last instance of the date
        MsgBox rFind.Address
    Else
        MsgBox "Not Found"
    End If
    
    
End Sub

 

[Malen Steptoe]

Hello and welcome.

You would use the Range.Find method ensuring the direction is set to xlPrevious.

So for example this code looks for the last instance of 01/12/19 in column A:

Sub LastDate()
    Dim d As Date, rFind As Range
    
    
    d = "01/12/19"
    
    Set rFind = Range("A:A").Find(What:=d, SearchDirection:=xlPrevious)
    
    If Not rFind Is Nothing Then
        'Display the address of last instance of the date
        MsgBox rFind.Address
    Else
        MsgBox "Not Found"
    End If
    
    
End Sub

That worked for find it so thank you for that. What I would like to do now is insert a new row below the located cell instead of the msgbox. I thought that would be relativity straight forward part but it seems to need to select the entire row initially and I cant work out how I would write that so that it was based on the found cell.

 

[gallen]

Apologies I missed that. Straight forward enough:

Sub LastDate()
    Dim d As Date, rFind As Range
    
    
    d = "01/12/19"
    
    Set rFind = Range("A:A").Find(What:=d, SearchDirection:=xlPrevious)
    
    If Not rFind Is Nothing Then
        'insert row below found cell
        [COLOR=#ff0000][B]rFind.Offset(1).Rows.Insert[/B][/COLOR]
    Else
        MsgBox "Not Found"
    End If
    
    
End Sub