Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching a sheet for rows that meet more than one column criteria
Hi All
I have a sheet with many rows of information across five columns. I want to search this sheet for the rows that match criteria in multiple columns. I can implement this myself but is there any VB support for doing this? Currently I maintain extra columns that concatenate the info together to form "keys" that I then search to find the rows that match the criteria. This works but it does not scale as I need a "key" column for every possible info combination I might need to search. Cheers for any ideas. Chrisso |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching a sheet for rows that meet more than one column criteria
Hi Chrisso,
Have you tried using Excel's native AutoFilter or Advanced Filter tools? You can, of course, use VBA to filter the data and apply the desired criteria. --- Regards, Norman "Chrisso" wrote in message oups.com... Hi All I have a sheet with many rows of information across five columns. I want to search this sheet for the rows that match criteria in multiple columns. I can implement this myself but is there any VB support for doing this? Currently I maintain extra columns that concatenate the info together to form "keys" that I then search to find the rows that match the criteria. This works but it does not scale as I need a "key" column for every possible info combination I might need to search. Cheers for any ideas. Chrisso |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching a sheet for rows that meet more than one column criteria
Oh - I see.
Do you mean: - apply the auto -filter in VB - specifiy the criteria for the auto-filter in VB - VB loop through the original list and the visible rows are the rows that match the criteria (look at the hidden property) Is that right? I want the result to for more VB logic not to show the user. Thanks for your response. Chris |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching a sheet for rows that meet more than one column criteria
Hi Chrisso,
Try turning on the macro recorder whilst you perform the requisite operations manually. This will provide code which may be edited to provide a more efficient, generic procedure. Instead of looping through the original range, you could use something like: '============= Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim RngOut As Range Set WB = Workbooks("MyBook.xls") '<<=== CHANGE Set SH = WB.Sheets("Sheet1") '<<=== CHANGE Set RngOut = SH.AutoFilter.Range.SpecialCells(xlVisible) Debug.Print RngOut.Address(0, 0) End Sub '<<============= --- Regards, Norman "Chrisso" wrote in message oups.com... Oh - I see. Do you mean: - apply the auto -filter in VB - specifiy the criteria for the auto-filter in VB - VB loop through the original list and the visible rows are the rows that match the criteria (look at the hidden property) Is that right? I want the result to for more VB logic not to show the user. Thanks for your response. Chris |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching a sheet for rows that meet more than one column criteria
Thanks Norman - that all makes sense. I have been reading about
SpecialCells and looks like I can really use it here. Thanks very much for your input. I would also like to ask you another question regarding Debug.Print. There is next to no information about how to use this module in Excel's help facitlity. Is it possible to use this object to lace your code with debug messages that are only seen when you set debug on? In this way you can see the debug when you like but a normal user does not? Cheers Chrisso |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching a sheet for rows that meet more than one column criteria
Hi Chrisso,
This is a different question and would more profitably be posed in a new thread. However, it is certainly possiblr to intersperse vode with debug.print instructions. The results of debug.print are shown in the Immediate window and, in normal operation, would not be visible to the user. --- Regards, Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting characters in multiple rows when rows meet specific criteria | Excel Worksheet Functions | |||
Selecting Rows that meet criteria | Excel Worksheet Functions | |||
count rows that meet certain criteria | Excel Discussion (Misc queries) | |||
How to copy rows that meet criteria to another sheet in Excel | Excel Worksheet Functions | |||
Copy rows that meet a certain criteria | Excel Programming |