Hello,
I'm trying to make a column that contains names that haven't been used from another column. This alone is very easy, but I'm trying to have it so there's no blanks in between as these columns feed into a UserForm. And it looks very unappealing to have a bunch of blank rows in-between entries.
So, as an example to what I'm trying to accomplish:
I have two sheets.
Sheet1 is like an attendance sheet that has a list of people who are present. Let's just say the range of this list is 'Sheet1'!A1:D12.
This range gets filled in with a UserForm that has the lists of attendees, which is added from Sheet2's range of 'Sheet2'!A3:A51.
Let's say Sheet2 is as follows:
A3 - Name1
A4 - Name2
A5 - Name3
A6 - Name4
A7 - Name5
Now, let's say Sheet1 only has these 2 entries in it:
A1 - Name1
A2 - Name4
What I'm trying to accomplish is having another range set up on Sheet2 with VBA that would sort out names that haven't been used yet. So that way the UserForm only shows names of people that aren't already on the attendance sheet.
So, with the example above, I would like the UserForm to only show the unentered names in Sheet2 in a new range as such :
A52 - Name2
A53 - Name3
A54 - Name5
And if Name3 got used on Sheet1, then Name5 would move up to A53 and A54 would be blank.
The UserForm would start attaching to this new Range from A52:A98 rather than the old range as well.
I have a couple of long and very messy ideas to accomplish this, such as having each cell equal a value then deleting rows that aren't needed or a long list of IFs.
I've been racking my brain trying to get this to work for over a week and I'd very much appreciate if someone could help me out with a much more simplistic approach!
Thank you!
I'm trying to make a column that contains names that haven't been used from another column. This alone is very easy, but I'm trying to have it so there's no blanks in between as these columns feed into a UserForm. And it looks very unappealing to have a bunch of blank rows in-between entries.
So, as an example to what I'm trying to accomplish:
I have two sheets.
Sheet1 is like an attendance sheet that has a list of people who are present. Let's just say the range of this list is 'Sheet1'!A1:D12.
This range gets filled in with a UserForm that has the lists of attendees, which is added from Sheet2's range of 'Sheet2'!A3:A51.
Let's say Sheet2 is as follows:
A3 - Name1
A4 - Name2
A5 - Name3
A6 - Name4
A7 - Name5
Now, let's say Sheet1 only has these 2 entries in it:
A1 - Name1
A2 - Name4
What I'm trying to accomplish is having another range set up on Sheet2 with VBA that would sort out names that haven't been used yet. So that way the UserForm only shows names of people that aren't already on the attendance sheet.
So, with the example above, I would like the UserForm to only show the unentered names in Sheet2 in a new range as such :
A52 - Name2
A53 - Name3
A54 - Name5
And if Name3 got used on Sheet1, then Name5 would move up to A53 and A54 would be blank.
The UserForm would start attaching to this new Range from A52:A98 rather than the old range as well.
I have a couple of long and very messy ideas to accomplish this, such as having each cell equal a value then deleting rows that aren't needed or a long list of IFs.
I've been racking my brain trying to get this to work for over a week and I'd very much appreciate if someone could help me out with a much more simplistic approach!
Thank you!