Expand and Collapse rows using a macro

[JeffK]

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]

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]

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]

Record a macro when you do it manually.

(Re)name that recorded Macro to "Autpen()"

 

[JeffK]

"Record a macro when you do it manually" sorry but my excel talent is
limited, how do I go about doing this?

 

[Dave Peterson]

In xl2003 menus.

Tools|Macro|Record new macro
Type in: Autpen
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 Autpen()

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 A1lastcol)(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 here:
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]

Dave, you've been very helpful. Thanks

In xl2003 menus.

Tools|Macro|Record new macro
Type in: Autpen
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 Autpen()

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 A1lastcol)(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 here:
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]

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]

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]

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]

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]

That's too bad, thanks for all your help.

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.

 

[Dave Peterson]

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
here:
http://contextures.com/xladvfilter01.html

If you want to try it via code:

Option Explicit
Sub Autpen()

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]

Dave, this recent macro works very nicely, it even hides the filter arrows
which makes the sheet look cleaner.

 

[JeffK]

Hi Dave,

Sorry to keep drawing from the well but you've been very helpful and my next
question refers to the autofilter you've helped me setup.

I'm using Excel 2003 which allows you to let users access autofilter when
the file is protected. I would like the users the ability to "show All" in
case they need to adjust a previous entry that's been filtered.

The problem is when the file is protected, "Use Autofilter" is selected, the
option from the Date->Filter->Autofiler and Show All are grey'd out.

I don't want the users the ability to unprotect the sheet.

Any suggestions

 

[JeffK]

me again Dave, Don't worry about this post

I've never used a form button before but figured it out to solve this
problem. I recorded a macro to unprotect and Show all then protect it once
again. Then inserted a form button a the top and programed is using the new
macro.

Somehow it works

 

[Dave Peterson]

Glad you found a way!

me again Dave, Don't worry about this post

I've never used a form button before but figured it out to solve this
problem. I recorded a macro to unprotect and Show all then protect it once
again. Then inserted a form button a the top and programed is using the new
macro.

Somehow it works