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