CODEWITHSUNDEEP
vbaexcel
pashew
pashew

Reputation: 43

Find last row of specific range of data excel

I have an Excel sheet that contains these data (attached Picture) enter image description here

I want to have the selected row in (Remaining sheet) The selected row is last data of (35,000IQD) in erbil city.

That means I want to have last row of in (specific) city with specific(card type) (there is two row of data of erbil city with card type 35,000 I want to give me the last one) Thanks in advance.

Upvotes: 0

Views: 980

Answers (2)

Anastasiya-Romanova 秀
Anastasiya-Romanova 秀

Reputation: 3378

If I understand your question correctly, then you try this code:

Sub MatchingCityAndCardType1()
Dim City As String, i As Long, Last_Row As Long
Last_Row = Cells(Rows.Count, "E").End(xlUp).Row

For i = Last_Row To 2 Step -1
    City = Cells(i, "C").Value
    If City = "erbil" And Mid(Cells(i, "E"), 1, 6) = "35,000" Then
        MsgBox "The card remaining of " & City & " city with card type " _
        & Cells(i, "E").Value & " is " & Cells(i, "J").Value & "."
        Exit For
    End If
Next i
End Sub

The output of this code is

enter image description here

As per pashew's request in the comment below, this code should work

    Sub MatchingCityAndCardType2()
    Dim City As String, i As Long, Last_Row As Long
    Last_Row = Cells(Rows.Count, "E").End(xlUp).Row

    For i = Last_Row To 2 Step -1
        City = Cells(i, "C").Value
        If City = "erbil" And Mid(Cells(i, "E"), 1, 6) = "35,000" Then
            'Set the range destination, Range(“A2”), depending on which 
            'range you want in Sheets(“Remaining”)
            Rows(i).EntireRow.Copy Destination:=Worksheets("Remaining").Range("A2")
            Exit For
        End If
    Next i
    End Sub

I put this code in the worksheet code module: Main Data Sheet.

Upvotes: 1

Tom
Tom

Reputation: 9898

There's a couple of ways you could approach this:

  • Using the method Anastasiya-Romanova has stated.
  • Using an Autofilter and special cells
  • Using an Array
  • And probably more

This one uses the find method which is faster then looping.

I'm assuming the card type is an integer value and not string, but if it is a string just updated the CardType as string and wrap the value in quotes.

Sub FindLastRow()
    Dim city As String, FirstAddress As String
    Dim CardType As Long
    Dim rng As Range
    Dim a

    city = "erbil"
    CardType = 35000

    With ThisWorkbook.Sheets("Main Data Sheet")
        Set rng = .Range(.Cells(1, 1), .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, 10))
    End With

    With rng
        Set a = .Find(what:=city, SearchDirection:=xlPrevious)

        If Not a Is Nothing Then
            FirstAddress = a.Address
            Do
                If a.Offset(0, 2) = CardType Then
                    ' Update this part with whatever you want to do to the last row
                    MsgBox a.Address
                    Exit Do
                Else
                    Set a = .FindNext(a)
                End If
            Loop While Not a Is Nothing And a.Address <> FirstAddress
        End If
    End With
End Sub

Upvotes: 0

Related Questions