Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 23 of 23

Thread: Remove duplicates on three criteria

  1. #21
    VBAX Tutor
    Joined
    Sep 2012
    Location
    London
    Posts
    263
    Location
    Hello everyone,
    I would like to thank each of you who has helped me in times of need and understanding of a problem that I could not do it alone.
    My great reverence and gratitude.
    I would like to ask you something and I hope to have an administrator who can answer my question that I can not find an answer and it consists in the fact that I ask myself one question: we do not know everything. We can not all, we need your help and support, but also can not afford a number of reasons and points to pay for our helped.
    Does this mean that we will be doomed and no one wants to come to the rescue, everything seems to be based only on financial part?
    So many years we have received help from you, and now ........
    I would be very grateful if someone really respond to my inquiry and I will be very grateful if you believe that you can help us uncomprehending disregarding things called money.
    With deep respect and appreciation.

  2. #22
    VBAX Tutor
    Joined
    Sep 2012
    Location
    London
    Posts
    263
    Location
    Hello friends,
    many asking for some help on this macro.
    Everything in it works very, very correctly, but when I rearrange the columns he makes only the specified criteria -> exactly as it should be.
    I ask for your help because I do not know how and where to add something that when it starts to perform a specified criteria to move my texts range from A to AC. Ie after 4,7,10 everything works very properly, but I just moved into this range information for each line and then deleting it to AC.
    I will repeat the macro is incredible, but do not know how under the same conditions to rearrange information for each line in this range of columns.
    Many ask for your assistance.
    Thank you in advance.
    Quote Originally Posted by k0st4din View Post
    Sub Dublikati_psihiatri_J()
    'Comment marks Description:
         '' (2 marks.) Instructions to K0st4din. Delete on satisfaction.
         '''' (4 marks.) Section Heading or major code segment.
         ' (1 mark.) Programmer reason for choosing to code this way.
         
         '' In VBA Editor, move Section Headings to far left, (Delete leading spaces.)
         
         
        Const DateCol As Long = 4 ''If sheet layout changes, only change these Constants
        Const NumCol As Long = 7 ''to maintain code.
        Const CodeCol As Long = 10
        Const ShtName As String = "my name sheet"
         
         
        Dim sh As Worksheet
        Dim lastrow As Long
        Dim rw As Long 'Row Index
         
         
        Set sh = Sheets(ShtName)
        Application.ScreenUpdating = False
         
         ''''Sort for Processing
        With sh
            lastrow = .Cells(Rows.Count, "J").End(xlUp).Row
             
             
             'Changed Sort Order1 to Descending to preserve "no delete" rows
            Range("A1").CurrentRegion.Sort Key1:=Columns(NumCol), Order1:=xlDescending, _
            Key2:=Columns(CodeCol), Order2:=xlAscending, _
            Key3:=Columns(DateCol), Order3:=xlAscending, _ ' here makes ordering range A through J - exactly as it should be, but how this layout making 
            Header:=xlYes ' me move information for each row to AC?
             
             
             '''''Processing. Delete Rows per Criteria.
            For rw = lastrow To 2 Step -1
                 'Fixed Cell assignments. Column J is not Columns(11)
                If .Cells(rw, CodeCol).Value = .Cells(rw - 1, CodeCol).Value _
                And .Cells(rw, NumCol).Value = .Cells(rw - 1, NumCol).Value Then
                     'Next line Raises "Type Mismatch" Error when Anded with above?!?!
                    If Month(.Cells(rw, DateCol).Value) = Month(.Cells(rw - 1, DateCol).Value) Then
                         'Edit Resize as needed
                        .Cells(rw, 1).Resize(1, CodeCol + 19).Delete Shift:=xlUp 'here delete to column AC and is correctly
                    End If
                End If
            Next rw
             
             
             ''''Restore sort for viewing.
             '' Adjust sort as desired
            Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, _
            Key2:=Columns(DateCol), Order2:=xlAscending, _
            Key3:=Columns(NumCol), Order3:=xlAscending, _
            Header:=xlYes
             
        End With
        Application.ScreenUpdating = True
    End Sub

  3. #23
    Thank you - this was helpful

Posting Permissions

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