You response is a LITTLE confusing, but it may be that you didn't read the
entire post.
Your original question was:
I'm running a macro to process some data over several columns. At a midway
point of the code, I need the macro to bring up a list of possible names. The
user then can check the ones they do not want, click OK, and then the macro
will go on to delete all records connected to that name.
The names are in column A, and repeat.
How can I call up a list of all unique names in column A, with a check box
available for the user to scroll through and pick all the names they wish to?
In my response, I said:
I created a userform (called Form_Records)that contained one listbox called
Lst_Typeand a button called "Btn_Delete".
You can call the button "Btn_OK" and have the caption read "OK",so that once
the user selects the keywords to remove, they then "Click OK".
Basically, the approach is:1) Read through data and collect "unique" values
from column "A"2) Open a userform and the values to a listbox.3) Once items are
selected, the "OK" button will then store list of items to be deleted.4) Cycle
through data and delete rows containing selected keywords.
The technique (or "trick") here is in collecting of the list of unique
values.The most common method is to create an array and add the items to the
array.However, to check to see if the item is already in the array requires to
loop through the array:
Flag_Exists = falseFor inx = 0 to ubound(strArray) if (NewItem =
strArray(inx)) then Flag_Exists = true Exit For end ifnext inxif (not
flag_Exists) then 'Add Item to strArrayend if
This works for small data sets, but if you have 5000 rows, the code has to loop
through the array 5000 times!(you can do things to reduce the number of
iterations, like: declare the array without a "size" then redim the array and
increase the size each time you add an item, or declare the array with a size
larger than the anticipated number of unique values and initialize the array
with blanks. Then break out of the above loop when the array value is blank.
I like using the Dictionary Object. It eliminates the need for looping through
an array, checking values, and you don't have to anticipate the maximum number
of values.It's be REALLY COOL if the object had a "sorted" property, but I
guess you can't have it all!Paul-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
On Friday, October 14, 2016 4:39 PM, kalimotxo <jamison.foll...@gmail.com>
wrote:
Hi Paul, this sounds pretty good. I'm new to working with these type of
objects, so I'll have to read through clearly. One thing I notice, if I
understand your code correctly, is that I do not want to have to click a button
to run the code, but rather have it in the middle of my sub(), or else call it
from my sub(). But once I have it working, I can probably figure that out.
Work done for the day, so I'll let you know Monday if it was successful. But I
wanted you to know I appreciate the prompt response!--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
