Hide blank cells in a table

[kalim]

Hi Excel users.

I have these two macros that will hide/unhide blank cell in a table.
What I am tryng to do is combine them so the user only needs to click one button and not two.

Sub Hide_blank_rows()
 
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:= _
"<>"
End Sub
 
Sub Show_blank_rows()
 
    ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2
End Sub

I have tried with an if statatement, but I get an error message.

Sub Hide_show_blank_rows()
 
if ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2 then
 
ActiveSheet.Shapes("Button 2").TextFrame.Characters.Text = "Unhide"
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:= _
"<>"
 
Else
 
ActiveSheet.Shapes("Button 2").TextFrame.Characters.Text = "Hide"
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2
 
    End If
End Sub

Any suggestions?

Thanks.

 

[cgcamal]

Hi kalim,

This code assumes that "Table 2" begins in column A, for this I've defined a range called "Table2" from A1:A21,
even when the whole table goes from A1:E21. You could use Columns("A:A") or Range("A1:A21") instead of
Range("Table2"). In this case is not needed to define range "Table2".

Sub Hide_Unhide()
[COLOR=Green]'Hl=Hidden lines[/COLOR]
    Hl = Range("Table2").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = False
    
    If Hl = True Then
        Range("Table2").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
    Else
        Range("Table2").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = False
    End If
End Sub

It can be adapted to your needs, if not works for you, please show a sample of your sheet.

Hope this helps.

Regards.

 

[Scott R]

Dim tbl As ListObject

Set tbl = ActiveSheet.ListObjects("Table2")
With tbl
    If .AutoFilter Is Nothing Then
        MsgBox "Filters are not enabled."
    ElseIf .AutoFilter.Filters(2).On = False Then
        .Range.AutoFilter Field:=2, Criteria1:="<>"
    Else
        .Range.AutoFilter Field:=2
    End If
End With

 

[kalim]

Thanks for the reply.

Both work perfectly thanks again.