Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
hide rows based on value
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
hide rows based on value
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
hide rows based on value
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
hide rows based on value
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide Rows based on value, but not using autofilter | Excel Worksheet Functions | |||
Hide Rows based on value | Excel Discussion (Misc queries) | |||
hide rows based on cell value | New Users to Excel | |||
hide rows based on value in cell | Excel Discussion (Misc queries) | |||
How to Hide Rows based on conditional value in cell | Excel Programming |