Turn on the macro recorder (tools=>macro=>record a new macro). Choose this
workbook for the location.
apply the autofilter manually.
Turn off the macro recorder by repeating the above or if the floating
toolbar has been produced, use that.
now go to the VBE and examine the code.
It will show you how to apply an autofilter using code.
If you have multiple complex conditions, it is often useful to put a formula
in the next available column that can evaluate several conditions to return a
true or false to indicate if that row should be included or not.
If you are doing this in code, you can set up all the conditions, then use a
technique such as JLatham has shown or autofilter on that column(s).
The advanced filter can allow you to specify multiple conditions, however,
another way may be to copy all the data to another sheet, then continue to
cull it down as conditions are specified (delete rows that don't meet that
condition - assume all conditions are AND related).
The alternative to copying to another sheet and would be to hide rows in the
exitsting sheet. I don't think it really matters one way or another whethe
you do it inplace or copy to another sheet. Obviously working on a copy
means the original should never get contaminated.
--
Regards,
Tom Ogilvy
"GrandMaMa" wrote:
> J. Latham;
> Thanks for getting back. Earlier I needed some
> recommendations, but no one got back to me. I will be need to print a report
> on selected students. The first criteria is to select all of the test
> criteria. With your help I have part of the first step completed. There are
> 15 more conditions I must test for.
>
> Question: Is it better to create another work sheet or just select the
> students I need from my current work sheet? Naturally I do not want to
> change any information in the worksheet, just get a print out of the selected
> students.
>
> Also is there a way I can use the AutoFilter in a Macro?
>
> If you are wondering why, the software vendor we have used for the past 15
> years is going out of business. We are also converting from an IBM system to
> individuals PC's for the Guidance Department.
>
> Thanks Again;
>
> Granny
>
> "JLatham" wrote:
>
> > It's difficult to give you "the" answer without knowing how you want to
> > handle matches. You say you can't use AutoFilter, so we'll just assume you
> > want matched cells 'noticed' for the moment. This code physically moves cell
> > to cell down the column - there are definitely more efficient, faster ways to
> > do this with Range objects, but since this is 2nd time out for you <g>.
> >
> > Dim lastRow as Long
> >
> > 'you get your TestClass value as before here
> >
> > lastRow = Range("L" & Rows.Count).End(xlUp).Row
> > 'if using Excel 2007, substitute:
> > lastRow = Range("L" & Rows.CountLarge).End(xlUp).Row
> > Range("L1").Select
> > Do Until ActiveCell.Row > lastRow
> > If ActiveCell.Value = TestClass Then
> > 'code to process matched data here
> > End If
> > ActiveCell.Offset(1, 0).Activate ' move to next row
> > Loop
> >
> >
> > "GrandMaMa" wrote:
> >
> > > User's Group
> > >
> > > This is only the second time I have ever worked with Excel so please have
> > > patience.
> > >
> > > I need to enter Filtering Criteria from an Input Box.
> > >
> > > Then match the contents of that variable to Column L (Will always be Column L)
> > >
> > > Because of what I eventually have to do I cannot use Autofilter.
> > >
> > > TestClass = InputBox("Enter ACT, International, S.A.T. or TAKS
> > > Classification")
> > >
> > > ? = What is the Format(Syntax) to compare the contents of TestClass to
> > > Column L.
> > >
> > > There will be eventually more code, I have to convert from another software
> > > application (That I have little knowledge of) to Excel.
> > >
> > > Thanks to all of ya' for assistance
> > >
> > > Granny
> > >
|
Similar Threads
