You may want to consider adding another column that contains a formula that
returns a true/false. Then you could make that formula as complex as you want
and filter on that column.
Or you could consider using advanced filter.
If you want to try it manually, Debra Dalgleish has a bunch of notes starting
he
http://contextures.com/xladvfilter01.html
If you want to try it via code:
Option Explicit
Sub Auto_Open()
Dim wks As Worksheet
Dim TempWks As Worksheet
Dim RngToFilter As Range
Dim CritRange As Range
Dim LastRow As Long
'change the name to what you need.
Set wks = Worksheets("SheetNameHere")
Set TempWks = Worksheets.Add
With wks
'remove any existing filter arrows
.AutoFilterMode = False
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set RngToFilter = .Range("A7", .Cells(LastRow, "A"))
End With
With TempWks
'set up criteria range
.Range("A1").Value = wks.Range("a7").Value
.Range("a2").Formula = "=""="""
.Range("a3").Value = "In progress"
.Range("a4").Value = "Approved"
Set CritRange = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With
RngToFilter.AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=CritRange, _
copytorange:="", _
Unique:=False
Application.DisplayAlerts = False
TempWks.Delete
Application.DisplayAlerts = True
End Sub
JeffK wrote:
That's too bad, thanks for all your help.
"Dave Peterson" wrote:
If you're using xl2003 or below, then you only get 2 criteria. I'm not sure
about xl2007.
So you could change the criteria2 parm to use "approved" instead of blanks.
JeffK wrote:
I want to add "approved" to the list for sorting. I've tried adding to the
macro but I keep getting it wrong:
RngToFilter.AutoFilter field:=1, Criteria1:="In Progress", _
Operator:=xlOr, Criteria2:=""
Could you help me a bit further.
Much appreciated.
"Dave Peterson" wrote:
These two lines determine the extent to filter:
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
So if you wanted to use column Z to determine the last row, you could use:
LastRow = .Cells(.Rows.Count, "Z").End(xlUp).Row
And if you wanted to use row 4 to determine the last column:
LastCol = .Cells(4, .Columns.Count).End(xlToLeft).Column
And since your data starts in row 8, then the last header row will be 7, so
you'd have to use this line:
Set RngToFilter = .Range("A7", .Cells(LastRow, LastCol))
JeffK wrote:
Thanks for the links, will be spending some time with those.
"I also determined the range to filter by looking at entries in column A and
row
1. If you have to use a different column or row, change those lines with
..end(xlup) and .end(xltoleft)."
My data starts in A8 with column A having the values that will be filtered
but I don't know what to change in your instruction above.
I copies the code as is to the workbook but nothing happens when I open the
file.
"Dave Peterson" wrote:
In xl2003 menus.
Tools|Macro|Record new macro
Type in: Auto_Open
in the macro name
and choose to store macro: "in this workbook"
You'd end up with code that would probably work--but may fail some times.
Instead of that, how about using this:
Option Explicit
Sub Auto_Open()
Dim wks As Worksheet
Dim RngToFilter As Range
Dim LastRow As Long
Dim LastCol As Long
'change the name to what you need.
Set wks = Worksheets("SheetNameHere")
With wks
'remove any existing filter arrows
.AutoFilterMode = False
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set RngToFilter = .Range("A1", .Cells(LastRow, LastCol))
RngToFilter.AutoFilter field:=1, Criteria1:="In Progress", _
Operator:=xlOr, Criteria2:=""
End With
End Sub
You'll have to put the code into your workbook's project. And change the name
of the worksheet.
You'll notice that I removed any existing filter arrows and applied them where I
wanted--just in case someone was doing something with MY <vbg data!
I also determined the range to filter by looking at entries in column A and row
1. If you have to use a different column or row, change those lines with
..end(xlup) and .end(xltoleft).
And I filtered from A1:(lastcol)(lastrow) looking for "in progress" or empty (to
match your posts).
If you're new to macros:
Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html
David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm
(General, Regular and Standard modules all describe the same thing.)
Macros will have to be enabled when the user opens the workbook.
And if you're using xl2007, make sure you save it as a workbook that can contain
macros (.xls or .xlsm or .xlsb (I think)).
JeffK wrote:
"Record a macro when you do it manually" sorry but my excel talent is
limited, how do I go about doing this?
"Dave Peterson" wrote:
Record a macro when you do it manually.
(Re)name that recorded Macro to "Auto_Open()"
JeffK wrote:
Thanks for your response Dave. I'm familiar with autofilter, but I would
like to program it so it will do a filter automatically when the file opens.
"Dave Peterson" wrote:
It sounds like you could use data|filter|autofilter (xl2003 menus) to hide/show
the rows you want.
The rows actually hide (not a rowheight of 1), but that's been ok for me.
Select your data (include the last header row)
data|filter|autofilter
Then use the arrow keys in the last header row to show/hide what you want.
Debra Dalgleish shares some getstarted tips:
http://contextures.com/xlautofilter01.html
JeffK wrote:
I have a Spreadsheet that as time goes on will grow quite large. In Column
A, the data entry person can choose from 4 options (In progress, Sold,
Cancelled, Declined). I would like to have all rows (starting from row 8) to
collapse to a height=1 except for those that are "In Progress".
I would be helpful if the rows would only collapse when file is closed and
reopened.
Your help is appreciated.
--
Dave Peterson
.
--
Dave Peterson
.
--
Dave Peterson
.
--
Dave Peterson
.
--
Dave Peterson
.
--
Dave Peterson