Searching a sheet for rows that meet more than one column criteria

[Chrisso]

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

 

[Norman Jones]

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.

 

[Chrisso]

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

 

[Norman Jones]

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
'<<=============

 

[Chrisso]

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

 

[Norman Jones]

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.