Filtering question

jimellem

New Member
Joined
Sep 8, 2006
Messages
8
I know this is probably covered somewhere else, so bear with me please. . . . or just don't answer this and link me somewhere else. . . .

I have nice ~4000 row database with information. Each row can be identified by a text code. Say some jerk in another department wants me to find 100 rows of data based on these 100 text codes. The text codes are totally random, so you need a way to filter to just get that data.

I know to use Excel's advanced filter capabilities you need to have the cells in this format ="TEXTCODE" I also know its a pain in the rear end (if not impossible?) to insert quotation marks using a simple macro into a text string -- since Macros use the quotation marks to define the text string.

So is advanced filter not the way to go about this??? Or Macro, or. . . .well I don't really know.

Much obliged for the help and have a good weekend. .. ..
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Eh, you don't need quotes or = for advanced filter for text.:)

What's your actual question?
 
Upvote 0
Duh!!!!

Absolutely no question at all. I just got hit by a stupid-bomb.

I realized what I was doing wrong -- pretty much everything. I could blame Mircosoft's lame Excel help file, but I'll take full credit here.

All apologies. And I got the filter to work.
 
Upvote 0
Make a list of the wanted ID Codes and put this list on a Sheet in your DataBase named "List"
Insert another sheet and name it "Found"

In the code below the Sheet named: "Data" is the name of the sheet containing the Data Table to be searched, you can change this name!

The code assumes that the Data Table ID Codes are in Column "A" or 1.


Sub myFindFromLst()
'Standard module code [VBA editor (Alt-11), Insert - Module]!
'Find all the rows that have your ID Codes in the Data Table on Sheet: "Data"
' [the sheet name of the sheet that has your Data Table].
'Sheet Name: List has the list of all the ID Codes you want filtered.
'Then copy part of the found row to the next blank row on Sheet Named: Found, for a filtered list.

Dim myIDCode As Variant
Dim Cell As Object, r As Object
Dim myIDs As Range
Dim n&

'Get ID Codes from list .
Sheets("List").Select
Set myIDs = Sheets("List").Range("A1:A100")

Application.ScreenUpdating = False

For Each Cell In myIDs
myIDCode = Cell.Value

'Check DB for each ID Code on wanted list!
For Each r In Sheets("Data").UsedRange.Rows
n = r.Row

'Look at "ID Code" Column of DB for "myIDCode" [Column "A" or 1, in this case]!
If Sheets("Data").Cells(n, 1).Value = myIDCode Then
x = x + 1
'Copy the found row/record,
'to the sheet named [Found] in its next empty row.

Sheets("Data").Select
r.EntireRow.Copy _
Destination:=Sheets("Found").Range("A65536").End(xlUp).Offset(1, 0)
Else
End If
Next r
Next Cell

Application.CutCopyMode = True
Application.ScreenUpdating = True
GoTo myEnd

myEnd:
'How many found?
If x <> 0 Then
MsgBox " Found: " & x & ", ID Codes!"
End If

Sheets("Found").Select
End Sub
 
Upvote 0
Thanks Joe

I tried your vB code and that works really well.

I love multiple ways to skin a cat. . .
 
Upvote 0
Yes most applications now days includes many ways to do things Hot-Keys, Buttons, Pull-Downs, Menus... The same with code and utilities. We all think differently and have different levels of experiance, so we see different solutions.

I am found of saying: If you cannot go through the door, go through a window.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

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