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.

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

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
.

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

"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
.

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