Hi all,

Here's my problem...

I have written a nice spreadsheet for writing proposals for work. I would
like to create two macros.

"shrink list" and "expand list"

I have set all cells in column A to be a value based on column B. Once I
have completed my proposal, I would like to click "shrink list" to hide all
ROWS that have "0" (zero) as it's value. And then be able to click "expand
list" to show all rows again. Essentially, the macro will look only at column
A and hide all of the rows that show a zero.

I know that I can do this with autofilter, but this needs to be simple for
less knowledgeable users.

Can someone help?

Try:

Private Sub CommandButton1_Click() 'ShrinkList
Dim eRow As Long
Dim i As Long
On Error GoTo Exit_Click
Application.ScreenUpdating = False
eRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To eRow
If Cells(i, 1).Value = 0 Then
Rows(i).EntireRow.Hidden = True
End If
Next i
Exit_Click:
Application.ScreenUpdating = True
End Sub

Private Sub CommandButton2_Click() 'Expand List
Rows.Hidden = False
End Sub

Hope this helps
Rowan

MTomlinson wrote:
Hi all,

Here's my problem...

I have written a nice spreadsheet for writing proposals for work. I would
like to create two macros.

"shrink list" and "expand list"

I have set all cells in column A to be a value based on column B. Once I
have completed my proposal, I would like to click "shrink list" to hide all
ROWS that have "0" (zero) as it's value. And then be able to click "expand
list" to show all rows again. Essentially, the macro will look only at column
A and hide all of the rows that show a zero.

I know that I can do this with autofilter, but this needs to be simple for
less knowledgeable users.

Can someone help?

Will try...thank you! is this two seperate macros? will this allow me to
have two different buttons or just toggle?

"Rowan Drummond" wrote:

Try:

Private Sub CommandButton1_Click() 'ShrinkList
Dim eRow As Long
Dim i As Long
On Error GoTo Exit_Click
Application.ScreenUpdating = False
eRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To eRow
If Cells(i, 1).Value = 0 Then
Rows(i).EntireRow.Hidden = True
End If
Next i
Exit_Click:
Application.ScreenUpdating = True
End Sub

Private Sub CommandButton2_Click() 'Expand List
Rows.Hidden = False
End Sub

Hope this helps
Rowan

MTomlinson wrote:
Hi all,

Here's my problem...

I have written a nice spreadsheet for writing proposals for work. I would
like to create two macros.

"shrink list" and "expand list"

I have set all cells in column A to be a value based on column B. Once I
have completed my proposal, I would like to click "shrink list" to hide all
ROWS that have "0" (zero) as it's value. And then be able to click "expand
list" to show all rows again. Essentially, the macro will look only at column
A and hide all of the rows that show a zero.

I know that I can do this with autofilter, but this needs to be simple for
less knowledgeable users.

Can someone help?

It is two seperate events for two seperate buttons from the Control
Toolbox - CommandButton1 and CommandButton2. I guess if you wanted a
toggle button that could be arranged.

Regards
Rowan

MTomlinson wrote:
Will try...thank you! is this two seperate macros? will this allow me to
have two different buttons or just toggle?

"Rowan Drummond" wrote:


Try:

Private Sub CommandButton1_Click() 'ShrinkList
Dim eRow As Long
Dim i As Long
On Error GoTo Exit_Click
Application.ScreenUpdating = False
eRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To eRow
If Cells(i, 1).Value = 0 Then
Rows(i).EntireRow.Hidden = True
End If
Next i
Exit_Click:
Application.ScreenUpdating = True
End Sub

Private Sub CommandButton2_Click() 'Expand List
Rows.Hidden = False
End Sub

Hope this helps
Rowan

MTomlinson wrote:

Hi all,

Here's my problem...

I have written a nice spreadsheet for writing proposals for work. I would
like to create two macros.

"shrink list" and "expand list"

I have set all cells in column A to be a value based on column B. Once I
have completed my proposal, I would like to click "shrink list" to hide all
ROWS that have "0" (zero) as it's value. And then be able to click "expand
list" to show all rows again. Essentially, the macro will look only at column
A and hide all of the rows that show a zero.

I know that I can do this with autofilter, but this needs to be simple for
less knowledgeable users.

Can someone help?