#### [SOLVED] Find last row of specific range of data excel

By pashew

I have an Excel sheet that contains these data (attached Picture)

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.

#### @Anastasiya-Romanova 秀 2016-07-13 11:08:41

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

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.

#### @pashew 2016-07-13 11:35:21

Dear Sir i want to move these value to (remaining sheet) in one row Date city card type card NO. free Card remaining 27/09/2016 erbil 35,000 27 1 3 Thanks for your support

#### @pashew 2016-07-13 12:13:49

Dear @Anastasiya-Romanova i want to move these value to (remaining sheet) in one row (27/09/2016 / erbil / 35,000 / 27 / 1 / 3) and i want to move the others like (erbil -- 4,8000)(soran --35,000)(soran-- 48,000) and so on (last entered data with filter of city and card type)you can select last entered data by date in picture there are 3 columns with (erbil-35,000) i want to move the last one Thanks for your kind support

#### @Anastasiya-Romanova 秀 2016-07-13 12:25:36

Dear Mr. @pashew, the 2nd code does the job by copying entire row 8 in "Main Data Sheet" to row 2 in "Remaining" sheet. But I don't understand your next request. Can you give me the picture of the data that you want to move instead of explaining using words?

#### @pashew 2016-07-13 12:40:18

this line don't work givs error Sheets(“Main Data Sheet”).Rows(i).EntireRow.Copy

#### @pashew 2016-07-13 13:09:39

Amazing it's working Can i make it give me some of these data because i don't need all of theme and can you make it just give me data not format cells , in the remaining sheet i have different style thank you again you really helped me thank you

#### @Anastasiya-Romanova 秀 2016-07-13 13:34:30

@pashew In my opinion, you should ask another question for that request but make sure this time state clearly what you want and show your attempt. I'm glad I can be of help

#### @pashew 2016-07-14 05:47:23

Dear @Anastasiya-Romanova 秀 .. thank you for kind support i have what i need ..... thank you again :)

#### @pashew 2016-07-19 12:16:19

Dear @Anastasiya-Romanova 秀 ... what if i add one more condition like (name)? that mean (city , name and card type)----thank you in advance

#### @Anastasiya-Romanova 秀 2016-07-19 12:33:52

@pashew Add a variable `Name = Cells(i, "D").Value` before the loop and change the condition to for example `If City = "erbil" And Mid(Cells(i, "E"), 1, 6) = "35,000" And Name = "dana" Then`

#### @pashew 2016-07-24 07:31:22

Dear @Anastasiya-Romanova 秀 . you really helped me . i have one last question ** :) ** ... what if i want to create another range of sheets based on monthly(create sheet for jan , feb ....and containing the whole data of this month) ? that mean 2 range of sheets the first range based on row(d) and the second range based on date (row c). i asked many people they didn't answer me....** thank you for your Kind support **

#### @Anastasiya-Romanova 秀 2016-07-24 10:08:39

@pashew I've just got online. Where is that question? Can you post it & make sure this time you state clearly your request so I can help you

#### @pashew 2016-07-24 12:22:10

Dear @Anastasiya-Romanova 秀 . you really helped me . i have one last question ** :) ** ... what if i want to create another range of sheets based on monthly(create sheet for jan , feb ....and containing the whole data of this month) ? that mean 2 range of sheets the first range based on row(d) and the second range based on date (row c). i asked many people they didn't answer me....** thank you for your Kind support **

#### @Anastasiya-Romanova 秀 2016-07-24 12:38:40

@pashew You kept repeating your question but I'm not really sure what are asking for that's why I ask you to post another question regarding your problem so that I can fully understand your request. I'll help you if I really understand your question completely

#### @pashew 2016-07-24 13:03:52

Sorry i am confused hhhh. see this link(stackoverflow.com/questions/38499331/extract-to-sheets-‌​by-month) and you understand me :)

#### @pashew 2016-07-24 14:44:41

:my question in mentioned page is blocked if you can answer it here it will be greate

#### @Anastasiya-Romanova 秀 2016-07-24 15:04:34

Sorry, I couldn't help. It's out of my coding skills. Maybe you can update your question by adding flowchart of your algorithm so that someone here can help you.

#### @pashew 2016-07-24 15:11:13

i am really appreciate your support and thank you again

#### @Tom 2016-07-13 11:37:39

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
Do
If a.Offset(0, 2) = CardType Then
' Update this part with whatever you want to do to the last row
Exit Do
Else
Set a = .FindNext(a)
End If
End If
End With
End Sub
``````

#### @pashew 2016-07-13 12:37:56

Dear @Tom i want to move these value to (remaining sheet) in one row (27/09/2016 / erbil / 35,000 / 27 / 1 / 3) and i want to move the others like (erbil -- 4,8000)(soran --35,000)(soran-- 48,000) and so on (last entered data with filter of city and card type)you can select last entered data by date in picture there are 3 columns with (erbil-35,000) i want to move the last one Thanks for your kind support

### [SOLVED] How do I finding the last column with data?

• 2012-08-13 00:33:45
• Neat Machine
• 343387 View
• 47 Score
• Tags:   excel vba

### [SOLVED] How can I find last row that contains data in a specific column?

• 2008-09-16 10:54:53
• Lipis
• 349226 View
• 64 Score
• Tags:   excel vba

### [SOLVED] Last not empty cell (column) in the given row; Excel VBA

• 2011-02-02 09:09:21
• user188962
• 55976 View
• 15 Score
• Tags:   excel vba excel-vba

### How to save as only last row of excel data in excel vb?

• 2016-04-26 15:09:33
• 129 View
• 0 Score
• Tags:   excel-vba vba excel