Toggle Button to Remove Empty Spaces

[Daylon]

Hey everyone, I'm currently trying to create a toggle button that will hide all empty rows in the range of A5:A41 (including formulas). Once I get that sorted out I would like to name the toggle button "Remove empty Cells" and "Open Empty Cells"

Here is a picture of the spreadsheet I'm using and the code that I currently have, but is not working.

 

[DanteAmor]

Hi @Daylon. Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try this:

Private Sub ToggleButton10_Click()
  Dim myrange As Range, c As Range
  
  Set myrange = Range("A5:A41")
  With ToggleButton10
    If .Caption = "Remove empty Cells" Then
      .Caption = "Open Empty Cells"
      For Each c In myrange
        c.EntireRow.Hidden = c.Value = ""
      Next
    Else
      .Caption = "Remove empty Cells"
      myrange.EntireRow.Hidden = False
    End If
  End With
End Sub

Note Code Tag:

In future please use code tags when posting code, instead of an image.​

How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.​

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[Daylon]

Hi @Daylon. Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try this:

Private Sub ToggleButton10_Click()
  Dim myrange As Range, c As Range
 
  Set myrange = Range("A5:A41")
  With ToggleButton10
    If .Caption = "Remove empty Cells" Then
      .Caption = "Open Empty Cells"
      For Each c In myrange
        c.EntireRow.Hidden = c.Value = ""
      Next
    Else
      .Caption = "Remove empty Cells"
      myrange.EntireRow.Hidden = False
    End If
  End With
End Sub

Note Code Tag:

In future please use code tags when posting code, instead of an image.​

How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.​

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

Dante, Your code worked well. I appreciate your assistance. The only small thing that I'm trying to tweak is that the toggle button is light gray (depressed). I'm trying to make it so that the first state of the button is the darker gray color (un-depressed). Please let me know your thoughts.

 

[Daylon]

Update: I was able to figure out the color differences. Thanks for the help.

 

[DanteAmor]

The only small thing that I'm trying to tweak is that the toggle button is light gray (depressed). I'm trying to make it so that the first state of the button is the darker gray color (un-depressed). Please let me know your thoughts.

Try:

Private Sub ToggleButton10_Click()
  Dim myrange As Range, c As Range
  
  Set myrange = Range("A5:A41")
  With ToggleButton10
    If .Caption = "Remove empty Cells" Then
      .Caption = "Open Empty Cells"
      .BackColor = &H8000000F   'light gray
      For Each c In myrange
        c.EntireRow.Hidden = c.Value = ""
      Next
    Else
      .Caption = "Remove empty Cells"
      .BackColor = &H808080     'darker gray
      myrange.EntireRow.Hidden = False
    End If
  End With
End Sub