hide rows based on value

[MTomlinson]

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?

[Rowan Drummond]

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?

[MTomlinson]

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?
>

[Rowan Drummond]

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?
>>