Using Toggle Button to Hide/Unhide blank rows

[gboudreau]

Ok, I have searched the forum, tried using Excel Help but just can't quite figure it out. What I have created is a project management timeline spreadsheet. what i want to do is use a toggle button to hide and unhide any rows that do not contain any information in column A. sometimes not all the rows will be used this depends on the amount of task for the project.

The following is what I have so far but just cant figure out the rest. If I click more than once ALL rows are hidden.

Private Sub ToggleButton1_Click()

Dim myRg As Range
'Change the [A1], [A65536] to your range
Set myRg = Range([A1].End(xlDown), [A80].End(xlUp))
On Error Resume Next
Set myRg = myRg.SpecialCells(4)
If Err = 0 Then
myRg.EntireRow.Hidden = True
Else
MsgBox "No blanks"
End If
Set myRg = Nothing

End Sub



Additionally, once I have this toggle working I would like to be able to protect the sheet and have the toggle still work.

Any ideas?

Thanks

 

[mcorriga]

The code you posted worked for me, for hiding the rows at least, and it didn't hide everything when ran again, but I wrote it with a slightly different approach to see if that works for you. I also included code to unprotect and protect the sheet so you can run it when its protected. Just put in the correct sheet names and a password for the unprotect and protect commands.

Private Sub ToggleButton1_Click()

Sheet1.Unprotect ("abc")

Dim myRg As Range
'Change the [A1], [A65536] to your range
Set myRg = Range([A1].End(xlDown), [A80].End(xlUp))

For Each cell In myRg
If IsEmpty(cell) Then
cell.EntireRow.Hidden = True
End If
Next cell

Set myRg = Nothing

Sheet1.Protect ("abc")

End Sub

 

[gboudreau]

Thanks for the input but unfortunately it didnt work. I created another toggle button and copied and pasted the code you provided and received a "Compile error: Variable not defined" dialog box and the debug highlights "cell" on the For Each cell In myRG line.

I may forgot to mention that I am using Excel 2003 as I am not sure if that has an impact.

I am able to get what I want to work except using a command button and macros.

I created a command button to Hide All blank rows:

Sub hideblankrowsincol()
Dim myRg As Range
'Change the [A1], [A65536] to your range
Set myRg = Range([A1].End(xlDown), [A80].End(xlUp))
On Error Resume Next
Set myRg = myRg.SpecialCells(4)
If Err = 0 Then
myRg.EntireRow.Hidden = True
Else
MsgBox "No blanks"
End If
Set myRg = Nothing
End Sub


I then created a Show all rows command button:


Sub Showallrows()
'
' Showallrows Macro
' Macro recorded 6/20/2012 by Glenn
'
'
Cells.Select
Selection.EntireRow.Hidden = False
End Sub

I would prefer to use a toggle button to do the same thing. I know it is possible as I remember using a sheet that does the same thing I am after, I just cant find the sheet other wise I would review the code. Anything assistance is greatly appreciated. Thanks

 

[Sandcastor]

GOT ONE FOR YOU!

Private Sub ToggleButton2_Click()

Dim X As Integer
Dim Y As Integer
Application.ScreenUpdating = False
If ToggleButton2.Value = True Then
On Error Resume Next
For Each Cell In Range("K26:K61")
    If Cell > 0 Then
        Cell.EntireRow.Hidden = False
    Else
        Cell.EntireRow.Hidden = True
        X = X + 1
        End If
Next
Else
    Range("K26:K61").EntireRow.Hidden = False
End If
If ToggleButton2.Value = True Then
On Error Resume Next
For Each Cell In Range("K62:K95")
    If Y < X Then
        Cell.EntireRow.Hidden = False
        Y = Y + 1
    Else
        Cell.EntireRow.Hidden = True
        End If
Next
Else
    Range("K62:K95").EntireRow.Hidden = True
End If
Application.ScreenUpdating = True
End Sub

This is a direct paste from my first VBA project, lots of credits to JS411 and Colo.
Okay, so what this script does is checks column K, rows 26:61. This is the quantity range on my invoice. For each blank it hides the row, then increments a counter (X). After that, it expands a previously hidden set of rows (62:95), one by one, so that for each hidden row, a blank appears at the bottom. This way, my invoice stays exactly one page long, with subtotals and taxes and totals at the bottom.

When the button is Un-Toggled, the ELSE kicks in, and it unhides all hidden invoice lines, and re-hides the blank lines.

Maybe if you only want to hide, try this next one. I haven't tried it, but let me know. The range is in the A column, so make sure your row number are correct:

Private Sub ToggleButton2_Click()

Application.ScreenUpdating = False
If ToggleButton2.Value = True Then
On Error Resume Next
For Each Cell In Range("A26:A61")
    If Cell > 0 Then
        Cell.EntireRow.Hidden = False
    Else
        Cell.EntireRow.Hidden = True
        End If
Next
Else
    Range("K26:K61").EntireRow.Hidden = False
End If

Application.ScreenUpdating = True
End Sub

 

[Sandcastor]

Here's a cleaned copy of my invoice so you can see how it works!
Field Ticket Dummy Copy


You will have to modify the script a bit:
change
If ToggleButton2.Value = True Then
to
If ToggleButton1.Value = True Then

and so on.

 

[gboudreau]

Thanks everyone for the inputs. I think I am almost there but I keep getting a "Compile error: Variable not defined" Sorry for my ignorance with VBS as I typically use the wizard to create macros. I used the code provided by Sandcastor and changed my range.
Below is what I pasted but received the compile error.
Private Sub ToggleButton2_Click()
Application.ScreenUpdating = False
If ToggleButton2.Value = True Then
On Error Resume Next
For Each Cell In Range("A19:A100")
If Cell > 0 Then
Cell.EntireRow.Hidden = False
Else
Cell.EntireRow.Hidden = True
End If
Next
Else
Range("A19:A100").EntireRow.Hidden = False
End If
Application.ScreenUpdating = True
End Sub

Again I am trying to create a toggle that will hide unused rows based on nothing entered in Column A and when untoggled show the unused rows. My range is A19:A100.

Thanks for the help.

 

[Rick Rothstein]

Again I am trying to create a toggle that will hide unused rows based on nothing entered in Column A and when untoggled show the unused rows. My range is A19:A100.

What is in A19:A100... data or formulas?

 

[gboudreau]

data only included in A19:A100

 

[mcorriga]

Using your code, you could use this. Just make sure there is a "Sheet3" and preferably hide the sheet.

Sub ToggleRows()
Dim chkValue As String
chkValue = Sheet3.Range("A1").FormulaR1C1
If chkValue = "Hidden" Then
'Show all rows
Cells.Select
Selection.EntireRow.Hidden = False
Sheet3.Range("A1").FormulaR1C1 = ""
Else
'Hide rows
Dim myRg As Range
'Change the [A1], [A65536] to your range
Set myRg = Range([A1].End(xlDown), [A80].End(xlUp))
On Error Resume Next
Set myRg = myRg.SpecialCells(4)
If Err = 0 Then
myRg.EntireRow.Hidden = True
Else
MsgBox "No blanks"
End If
Set myRg = Nothing
Sheet3.Range("A1").FormulaR1C1 = "Hidden"
End If
End Sub

 

[Rick Rothstein]

data only included in A19:A100

Then you can use this simple code to toggle visibility of the rows where the cells in range A19:A100 are blank...

Sub HideUnhideRowsIfColumnAisBlank()
  On Error Resume Next
  With Range("A19:A100").SpecialCells(xlBlanks).EntireRow
    .Hidden = Not .Hidden
  End With
  On Error GoTo 0
End Sub