Trying to sort out names that haven't been used from a list using VBA

Keojey

New Member
Joined
Sep 19, 2018
Messages
35
Office Version
  1. 365
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!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Would it be acceptable that the processing is carried out on the Userform when it is loaded?

Where is the list going on the Userform?
 
Upvote 0
Would it be acceptable that the processing is carried out on the Userform when it is loaded?

Where is the list going on the Userform?
Yes, that would totally fine as long the the end goal is still reached! The UserForm is just a simple list that holds all the names for the user to double-click on to to enter them into a selected cell on Sheet1. So, currently 'Sheet2'!A3:A51 is going right into the big ListBox that you can see in the picture of what the UserForm currently looks like here :
2022-02-02 09_29_31-Feeds.jpg.png
 
Last edited:
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,710
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top