Consulting

Results 1 to 5 of 5

Thread: compare date in number and product

  1. #1
    VBAX Tutor
    Joined
    Sep 2012
    Location
    London
    Posts
    237
    Location

    compare date in number and product

    Hello everyone ,
    I asked in another forum to make a macro and there helped me , but it turned out that it should be revised .
    Then I wrote again, but so far no reply.
    I would like to ask you if you can help me and adapt it slightly .
    Macro searches the specified column matches and if you find them , delete their ranks. I asked if it could be done so as to preserve the idea of the macro (to make exactly the same ) but to delete the rows of A:X, because after that point (column ) I have written other things that are constant and have to be there. I would be very grateful if you help me.
    Here is the link to my inquiry.
    Herre is macro:
    Sub deldupe2()
    Dim sh As Worksheet, lr As Long
    Set sh = Sheets(1) 'Edit Sheet name
    lr = sh.Cells(Rows.Count, 11).End(xlUp).Row
    sh.UsedRange.Sort sh.Range("K1"), xlAscending, Header:=xlYes
        For i = lr To 2 Step -1
            If sh.Cells(i, 11).Value = sh.Cells(i - 1, 11).Value And _
                sh.Cells(i, 7).Value = sh.Cells(i - 1, 7).Value And _
                sh.Cells(i, 4).Value = sh.Cells(i - 1, 4).Value Then
                Rows(i).Delete
            End If
        Next 
    End Sub
    Last edited by k0st4din; 02-23-2014 at 09:58 PM.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    Sub deldupe2() 
        Dim sh As Worksheet, lr As Long 
    
        Set sh = Sheets(1) 'Edit Sheet name
        lr = sh.Cells(Rows.Count, 11).End(xlUp).Row 
        sh.UsedRange.Sort sh.Range("K1"), xlAscending, Header:=xlYes 
        For i = lr To 2 Step -1 
    
            If sh.Cells(i, 11).Value = sh.Cells(i - 1, 11).Value And _ 
            sh.Cells(i, 7).Value = sh.Cells(i - 1, 7).Value And _ 
            sh.Cells(i, 4).Value = sh.Cells(i - 1, 4).Value Then 
    
                Cells(i, "A").Resize(,24).ClearContents 
            End If 
        Next 
    End Sub
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Tutor
    Joined
    Sep 2012
    Location
    London
    Posts
    237
    Location
    Hello,
    For starters many thanks, but not exactly what I want.
    Why? - Because after X text that I wrote to invert or remain empty spaces (cells), which totally confuses my work.
    When I do it manually, ie click in cells to obtain macro shows me an action which to me appears like this:
    Selection.Delete Shift:=xlUp
    Which is not true, because I do it by hand. Please again for assistance.
    P.S. - And one more thing in the first macro I click once and everything is deleted (except delete entire rows, rather than the desired range), while in your click twice.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    Sorry I don't understand most of that.

    I have no idea what X text is, or what ... invert or remain empty spaces (cells) ... means.

    I don't what you are saying about that line of code.

    I don't know what is meant by ... and everything is deleted (except delete entire rows, rather than the desired range) ... or why you have to click twice with my code.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Tutor
    Joined
    Sep 2012
    Location
    London
    Posts
    237
    Location
    I have tried to show it in the picture.
    In the first post I wrote about what is X (ranging from A to X - column).
    The question is not rearrange my writing after column X.
    This is only an example, but the cells in which to compare these coincidences are precisely.
    Many thanks for helping on your part.
    Attached Images Attached Images
    Last edited by k0st4din; 02-24-2014 at 11:53 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •