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.


Log in to reply
 

Looks like your connection to MangoLassi was lost, please wait while we try to reconnect.