Excel VBA

DustinB3403

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     as@      Course 1     Yes/No                       %
                                                                    Yes/No                        %
                                                                    Yes/No                                    %
John              Jacobs  js@       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?

dafyre

@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     as@      Course 1     Yes/No                       %
                                                                    Yes/No                        %
                                                                    Yes/No                                    %
John              Jacobs  js@       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 ?

DustinB3403

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

DustinB3403

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.

Mike Davis

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.

dafyre

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

DustinB3403

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

DustinB3403

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