By pashew


2016-07-13 10:32:01 8 Comments

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.

2 comments

@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

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.

@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

@Anastasiya-Romanova 秀 2016-07-13 11:50:52

@pashew Have a look my edited answer

@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

@Anastasiya-Romanova 秀 2016-07-13 12:49:47

@pashew My bad. I fix the code. It works fine now

@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 **

@pashew 2016-07-24 10:02:20

please help i really need it

@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
            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

@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

Related Questions

Sponsored Content

13 Answered Questions

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

  • 2008-09-16 10:54:53
  • Lipis
  • 335764 View
  • 59 Score
  • 13 Answer
  • Tags:   excel vba

1 Answered Questions

[SOLVED] Excel/VBA to Simplify Navigation to Specific Excel Cells

5 Answered Questions

[SOLVED] Excel VBA- Finding the last column with data

  • 2012-08-13 00:33:45
  • Neat Machine
  • 317464 View
  • 41 Score
  • 5 Answer
  • Tags:   excel excel-vba vba

2 Answered Questions

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

  • 2011-02-02 09:09:21
  • user188962
  • 52458 View
  • 15 Score
  • 2 Answer
  • Tags:   excel vba excel-vba

2 Answered Questions

[SOLVED] How do you find the last row number in column VBA?

  • 2018-04-20 04:54:50
  • SaladSnake
  • 2654 View
  • -2 Score
  • 2 Answer
  • Tags:   vba excel-vba excel

2 Answered Questions

[SOLVED] Excel VBA - Finding Last Non-Empty Row Based on Column Header

1 Answered Questions

[SOLVED] Delete Last 10 rows from Specific sheets - Excel

1 Answered Questions

[SOLVED] if the value exist retrieve last row of this value

2 Answered Questions

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

  • 2016-04-26 15:09:33
  • Schadrack Rurangwa
  • 100 View
  • 0 Score
  • 2 Answer
  • Tags:   excel-vba vba excel

5 Answered Questions

Sponsored Content