Excel VBA



  • Annoying as this is.

    I have an excel document that is pulled down regularly. It has user details, and course details (and sub course details).

    What I want to do is something like this. The sub course attempted is already filled in with a Yes or No, so I just need to keep the record there.

    Ideally everything would fill across the Row 1 in excel, so the report is as concise as possible, but I don't think its critical.

    First name | Last Name  | Email  | Course     | Sub Course Attempted?    |  Grade
    Anne             Smith     [email protected]      Course 1     Yes/No                       %
                                                                        Yes/No                        %
                                                                        Yes/No                                    %
    John              Jacobs  [email protected]       Course 1     Yes/No                                %
                                                                        Yes/No                                    %
                                                                        Yes/No                                    %
    

    This is what I've got.

    Sub CopyUnique()
        Dim s1 As Worksheet, s2 As Worksheet
        Set s1 = Sheets("Main")
        Set s2 = Sheets("Count")
        s1.Range("A:N").Copy s2.Range("A:N")
        s2.Range("A:D").RemoveDuplicates Columns:=1, Header:=xlNo
    End Sub
    

    Which works to remove the details I don't need, but everything moves up underneath what I've removed, when all I want is for it to clear the cells, not delete the empty ones.

    Any help?



  • @DustinB3403 said in Excel VBA:

    Annoying as this is.

    I have an excel document that is pulled down regularly. It has use details, and course details (and sub course details).

    What I want to do is something like this. The sub course attempted is already filled in with a Yes or No, so I just need to keep the record there.

    Ideally everything would fill across the Row 1 in excel, so the report is as concise as possible, but I don't think its critical.

    First name | Last Name  | Email  | Course     | Sub Course Attempted?    |  Grade
    Anne             Smith     [email protected]      Course 1     Yes/No                       %
                                                                        Yes/No                        %
                                                                        Yes/No                                    %
    John              Jacobs  [email protected]       Course 1     Yes/No                                %
                                                                        Yes/No                                    %
                                                                        Yes/No                                    %
    

    This is what I've got.

    Sub CopyUnique()
        Dim s1 As Worksheet, s2 As Worksheet
        Set s1 = Sheets("Main")
        Set s2 = Sheets("Count")
        s1.Range("A:N").Copy s2.Range("A:N")
        s2.Range("A:D").RemoveDuplicates Columns:=1, Header:=xlNo
    End Sub
    

    Which works to remove the details I don't need, but everything moves up underneath what I've removed, when all I want is for it to clear the cells, not delete the empty ones.

    Any help?

    Are the duplicates usually back to back? ie: the first one in cell A3 and the duplicate is in A4 ?



  • @dafyre Normally down, not side to side.

    For example.

    Anne Smith might be listed 6 times with the same email, and course title for rows 2-7.

    But we only need her listed once, but the custom class details we want to keep.



  • This is what I'm looking for.

    http://stackoverflow.com/questions/17748658/remove-duplicate-value-but-keep-rest-of-the-row-values

    Will see if I can get this going.


  • Service Provider

    You don't want to remove duplicates, you want to replace duplicates with a blank cell. I could code running down line by line and basically say if the value I'm looking at is the same as the value above it, replace that cell with "" . I'm sure there is a more efficient way.



  • @Mike-Davis said in Excel VBA:

    You don't want to remove duplicates, you want to replace duplicates with a blank cell. I could code running down line by line and basically say if the value I'm looking at is the same as the value above it, replace that cell with "" . I'm sure there is a more efficient way.

    That's what all of the snippets I have found actually do.



  • @Mike-Davis said in Excel VBA:

    You don't want to remove duplicates, you want to replace duplicates with a blank cell. I could code running down line by line and basically say if the value I'm looking at is the same as the value above it, replace that cell with "" . I'm sure there is a more efficient way.

    Yeah I think I have something that will work for the case, now that I've got it.

    I'll keep you posted.



  • Also this is what I've done, but modified the latter half of the page to keep everything in place.