Must be better way...Using Listbox Selection to Query Data

[Guest]

This is the scenerio:
A small example of what I am having problems with.

Data
====
A B C D
Company Name State City Zip
1 Yellow Co. NY New York 10111
2 Blue Inc CT Greenwich 06830
3 Green LLC GA Atlanta 78654
4 Gold Corp. WA Seattle 90768


I set up a multi page userform with 2 list boxes on each page. List
box 1 has all the companies available to analyze. User selects companies
to analyze which then gets transferred to List box2. I repeat this for
the other various data.

ie. Listbox3 (States available) --> Listbox4(States Selected)
Listbox5(Cities Avaialble) --> Listbox6(Cities Selected)
Listbox7(ZipCodes Available) --> Listbox8(ZipCodes Selected)


I then use the results of List box 2, 4, 6, and 8 to remove rows from
the spreadsheet which do not fit into the selected query.

Set LBox2 = ListBox2
Set LBox4 = ListBox4
Set LBox6 = ListBox6
Set LBox8 = ListBox8

CompanyCol = "A"

myList2Company = LBox2.List
myList4States=LBox4.List
myList6City=LBox6.List
MyList8Zip=LBox8.List

Dim DelCompRow(65000) As String
Dim CheckAgainComp(65000) As String
Dim LastRow As Long, r As Long

LastRow = ActiveSheet.UsedRange.Rows.Count
LastRow = LastRow + ActiveSheet.UsedRange.Row - 1

For r = 2 To LastRow
For Each Item In myList2Company
If Range(CompanyCol & r).Value <> Item And DelCompRow(r) <>
"NO" And CheckAgainComp(r) <> "NO" Then
DelCompRow(r) = "YES"
CheckAgainComp(r) = "NO"
Else
If Range(CompanyCol & r).Value = Item Then
DelCompRow(r) = "NO"
CheckAgainComp(r) = "YES"
End If
End If
Next Item
Next r

For r = 2 To LastRow
If DelCompRow(r) = "YES" Then
Rows(r).Clear
End If
Next r

DeleteEmptyRows

I then repeat the above code for the other column heading ie. States,
Zip...

My Question Is: Is there a better way to write the above code? The
problem with the above is that say the user selects 20 states, it has to
go through each row 20 times and then deletes it. I have to repeat the
same for all the other selected items in the listbox. With 4
listboxes, multiple items per listbox, and 50,000 rows of data in my
spreadsheet, this takes an extremely long time to complete and sometimes
crashes.
Any ideas on how this can be improved? Any help would be greatly appreciated!
Thank you.

 

[Tom Ogilvy]

It doesn't make sense to me why you would treat each of the columns
independently. If I delete Blue Inc, wouldn't I delete the whole row
(whether the user was able to rememeber not to select CT, Greenwich and
06830 or not).

It that is the case, why not use a single 4 column, multiselect listbox.
then you can just walk backwards through the list and delete the rows as
they are not selected.

 

[Guest]

Hi Tom,

Thanks for taking a look at my problem. I guess the end result of my script
is to have the user be able to query for example...Out of Companies 1 to 100
I want Company #4 and Company #55 and then I want to pull out data for those
two companies in 7 different states which reside in 4 different cities to
give me as an example the number of people working for the two companies in
the seven states in those 4 cities. I also want the user to be able to
choose the state of CT and to be able to include just Greenwich or every city
in Greenwich in the query. As another example, the user can choose all
available companies but then query out just those in 30 states and include
all cities to include in the query basically returning the number of people
working for all companies in the 30 states. I just seem to be rambling on
now. Thanks again for your help.