Hide Columns IF Row 2 onwards is Blank

**Wheelie686** · 05-01-2015, 12:31 AM

The following code works..sorta. I can see that it's hiding the columns I want/are blank but then it immediately unhides them. What I want is when I press the button once, it hides them and then press it again, unhides them. Any idea where I went wrong?

Sub ToggleButton1_Click()
If ToggleButton1.Value = True Then

Dim i As Long
    
For i = 2 To 12
    If Sheets("Comments").Columns(i).Rows(1).End(xlDown).Value = "" Then
    Columns(i).EntireColumn.Hidden = True
End If
    Next i
For i = 2 To 12
    Columns(i).EntireColumn.Hidden = False
    Next i

If ToggleButton1.Caption = "Hide Blank Columns" Then
        ToggleButton1.Caption = "Show All"
    Else
        ToggleButton1.Caption = "Hide Blank Columns"
    End If
    End If
End Sub

**LokeshKumar** · 05-01-2015, 01:39 AM

Can you upload the workbook with this code for better understanding.

**Wheelie686** · 05-01-2015, 07:27 AM

Here you go!

You can ignore the blue buttons (actually just shapes). They don't have code attached anymore. The grey toggle button is my issue, thanks!

**LokeshKumar** · 05-01-2015, 08:05 AM

Hi,

Can you check now it is working correctly?......Book1.xlsm

If your question is resolved, mark it SOLVED using the thread tools. Click on the star if you think some-1 helped you.

**Wheelie686** · 05-01-2015, 09:40 AM

not quite... for instance column C, E, I and K are blank whereas the others have a 1 in them. I only want the blank columns hidden.

**LokeshKumar** · 05-01-2015, 11:25 AM

Originally Posted by Wheelie686

not quite... for instance column C, E, I and K are blank whereas the others have a 1 in them. I only want the blank columns hidden.

Hey sorry for bad explanation from my end. I was expecting you to be a familiar with VBA so I just removed the unnecessary part.
You can easily put the if else condition to check the blank.
If still face any issue pls let us know...........

**JOHN H. DAVIS** · 05-01-2015, 11:05 AM

Maybe?

Sub Wheelie686()

Dim i As Long
If ToggleButton1.Value = True Then
    ToggleButton1.Value = False
Else
    ToggleButton1.Value = True
End If
If ToggleButton1.Caption = "Show All" Then
        For i = 12 To 2 Step -1
            Columns(i).Hidden = False
        Next i
ElseIf ToggleButton1.Caption = "Hide Blank Columns" Then
        For i = 12 To 2 Step -1
            If Cells(2, i) = "" Then
                Columns(i).Hidden = True
            End If
        Next i
End If


End Sub

**Wheelie686** · 05-01-2015, 03:55 PM

LokeshKumar, maybe it's me trying to be at work and mess with Excel at the same time but I just can't seem to get the coding right. I have some knowledge of VBA but I'm self taught through (mainly) this site and using the macro recorder. This is what I have so far. It hides the appropriate columns now but doesn't unhide anything.

Option Explicit

Sub ToggleButton1_Click()
    
    Dim i As Long
    
        If ToggleButton1.Value = True Then

        For i = 2 To 12

        If Sheets("Comments").Columns(i).Rows(1).End(xlDown).Value = "" Then
        Columns(i).EntireColumn.Hidden = True
        End If
            Next i
        
        If ToggleButton1.Value = False Then

            For i = 2 To 12
            
        Columns(i).EntireColumn.Hidden = False
            Next i
        End If
        
        If ToggleButton1.Caption = "Hide Blank Columns" Then
        ToggleButton1.Caption = "Show All"
            Else
                ToggleButton1.Caption = "Hide Blank Columns"
        End If
        End If
End Sub

**xladept** · 05-01-2015, 03:59 PM

Maybe:

If ToggleButton1.Value = False Then Columns.Hidden = False

**Wheelie686** · 05-01-2015, 04:45 PM

omg I forgot Else...literally forgot the word "Else". Thanks everyone... I guess I just needed to focus on what I'm doing.

Sub ToggleButton1_Click()
If ToggleButton1.Value = True Then

Dim i As Long
    
For i = 2 To 12
    If Sheets("Comments").Columns(i).Rows(1).End(xlDown).Value = "" Then
    Columns(i).EntireColumn.Hidden = True
End If
    Next i
Else
For i = 2 To 12
    Columns(i).EntireColumn.Hidden = False
    Next i

If ToggleButton1.Caption = "Hide Blank Columns" Then
        ToggleButton1.Caption = "Show All"
    Else
        ToggleButton1.Caption = "Hide Blank Columns"
    End If
    End If
End Sub

Now to figure out why the button name doesn't work right...

Hide Columns IF Row 2 onwards is Blank

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Hide Columns IF Row 2 onwards is Blank

Re: Hide Columns IF Row 2 onwards is Blank

Re: Hide Columns IF Row 2 onwards is Blank

Re: Hide Columns IF Row 2 onwards is Blank

Re: Hide Columns IF Row 2 onwards is Blank

Re: Hide Columns IF Row 2 onwards is Blank

Re: Hide Columns IF Row 2 onwards is Blank

Re: Hide Columns IF Row 2 onwards is Blank

Re: Hide Columns IF Row 2 onwards is Blank

Re: Hide Columns IF Row 2 onwards is Blank

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Hide columns if empty from Row 3 onwards vba

Hide non adjacent columns if all cell value is blank

Hide columns if all cells are blank or £0.00

Hide blank columns via loop

Bookmarks

Bookmarks

Posting Permissions