CODEWITHSUNDEEP
vbaexcel
daniel
daniel

Reputation: 13

if the value exist retrieve last row of this value

I want vba code to look for specific ranges of data if these data exist in main sheet then retrieve last row of data that mean based on conditions. For example(there are 3 row with "dler" I want to compare dler with three rows of second sheet if all exist retrieve the row of dler) that mean compare name with other rows and so on... The picture is two sheets the first one is (main sheet) and the second one is the table that the vba work on it to find data in (main sheet) I have this code but I don't know how change it to work with dynamic records.

Main and Search Worksheet Image

Sub Matching_name()
    Dim a_name As String, i As Long, j As Long, Last_Row As Long

    For i = Last_Row To 2 Step -1
        a_name = Cells(i, "B").Value
        If City = "dler" Then
            'Set the range destination, Range(“A2”), depending on which
            'range you want in Sheets(“Remaining”)
            Rows(i).EntireRow.Copy Destination:=Worksheets("Remaining").Range("A1")
            Exit For
        End If

    Next i

End Sub

Upvotes: 1

Views: 96

Answers (1)

Arun Thomas
Arun Thomas

Reputation: 845

This will copy the last matching rows

Sub Matching_name()

Dim i As Long, j As Long, k As Long, Last_Row As Long, temp As Long
Dim a_name As String, s_type As String, c_type As String

temp = 1
Last_Row = 6

For i = 2 To Last_Row
    Worksheets("Main Sheet").Activate
    a_name = Cells(i, 2).Value
    s_type = Cells(i, 5).Value
    c_type = Cells(i, 6).Value

    Worksheets("Search Sheet").Activate
    For j = 1 To 3
        If Cells(j, 1).Value = a_name And Cells(j, 2).Value = s_type And Cells(j, 3).Value = c_type Then
            Worksheets("Main Sheet").Activate
            Rows(i & ":" & i).Select
            Selection.Copy
            Worksheets("Remaining").Activate
            Rows(temp & ":" & temp).Select
            ActiveSheet.Paste
            temp = temp + 1
        End If
    Next j
Next i

Worksheets("Remaining").Activate
For x = temp To 1 Step -1
    y = 1
    While y <= temp
        If Cells(y, 2).Value = Cells(x, 2).Value And x <> y Then
            Rows(y & ":" & y).Delete
            y = y - 1
            temp = temp - 1
        End If
        y = y + 1
    Wend
Next x

End Sub

Upvotes: 1

Related Questions