Hiding Blank Rows in Certain Range thru Click Button

[nhinx]

Hi Everyone,

I got a VBA code from google and it works fine. However, I need to hide blank rows only in a specific range and the code below hide the entire range of rows. Thus anyone can amend the code below for my specific requirement? For example, I have a data from C4 to C10 and by doing so the blank rows from C11 to C15 will be hidden and by clicking again it will unhide.

Private Sub CommandButton1_Click()
If CommandButton1.Caption = "Hide Blank Rows" Then
Range("C4:C15").EntireRow.Hidden = True
CommandButton1.Caption = "Show Blank Rows"
Else
Range("C4:C15").EntireRow.Hidden = False
CommandButton1.Caption = "Hide Blank Rows"
End If
End Sub


Many thanks,
Nhinx

 

[JackDanIce]

Do you mean:

Private Sub CommandButton1_Click()
    
    With CommandButton1
        If .Caption = "Hide Blank Rows" Then
            Range("C11:C15").EntireRow.Hidden = True
            .Caption = "Show Blank Rows"
        Else
            Range("C11:C15").EntireRow.Hidden = False
            .Caption = "Hide Blank Rows"
        End If
    End With
    
End Sub

 

[nhinx]

Hi JackDanIce,

The Range should still be from C4:C15, if i will click the button which shows "Hide Blank Rows" the blank rows in Range C4:C15 should be hidden. When the button was switched to "Show Blank Rows"; then after clicking, all the blank rows from range C4:C15 that was hidden will be unhide.

Nhinx

 

[JackDanIce]

I've just tested your original code and it does hide and unhide with the caption updating for rows 4:15

Can you explain further what is not working?

 

[nhinx]

I have a data from Row C4 to C15 in which some of the cells has a value. For example I have data in C4, C6, C7, C10, C12, C14, C15. Then after clicking "Hide Blank Rows", the following rows C5, C8,C9, C11, C13, will be hidden. Then after it switched to "Show Blank Rows" by clicking the button again, all of the hidden rows will be shown again.

Thanks,

Nhinx

 

[JackDanIce]

Try:

Private Sub CommandButton1_Click()
    
    Dim rng     As Range
    Dim rngRow  As Range
    Dim rngHide As Range
    
    Set rngRow = Range("C4:C15")
    
    Application.ScreenUpdating = False
    
    With CommandButton1
        If .Caption = "Hide Blank Rows" Then
            For Each rng In rngRow
                If Len(rng.Value) = 0 Then
                    If rngHide Is Nothing Then
                        Set rngHide = rng
                    Else
                        Set rngHide = Union(rngHide, rng)
                    End If
            Next rng
            rngHide.EntireRow.Hidden = True
            .Caption = "Show Blank Rows"
            Set rngHide = Nothing
        Else
            rngRow.EntireRow.Hidden = False
            .Caption = "Hide Blank Rows"
        End If
    End With
    
    Application.ScreenUpdating = True
    
    Set rngRow = Nothing
    
End Sub

 

[nhinx]

Hi JackDanIce,

Thank you for the code. However, it has a Compile Error saying Next Without For which highlighting in yellow in Next Rng.

Thanks,
Nhinx

 

[JackDanIce]

Was missing an End If, try:

Private Sub CommandButton1_Click()
    
    Dim rng     As Range
    Dim rngRow  As Range
    Dim rngHide As Range
    
    Set rngRow = Range("C4:C15")
    
    Application.ScreenUpdating = False
    
    With CommandButton1
        If .Caption = "Hide Blank Rows" Then
            For Each rng In rngRow
                If Len(rng.Value) = 0 Then
                    If rngHide Is Nothing Then
                        Set rngHide = rng
                    Else
                        Set rngHide = Union(rngHide, rng)
                    End If
                End If
            Next rng
            rngHide.EntireRow.Hidden = True
            .Caption = "Show Blank Rows"
            Set rngHide = Nothing
        Else
            rngRow.EntireRow.Hidden = False
            .Caption = "Hide Blank Rows"
        End If
    End With
    
    Application.ScreenUpdating = True
    
    Set rngRow = Nothing
    
End Sub

 

[nhinx]

Hi JackDanIce,

This awesome. It works for my requirement. You're such an excel expert. Continue to share your knowledge.


Thank you very much,
Nhinx

 

[nhinx]

Hello Again JackDanIce,

I just have a litlle problem with the code. If all the rows from C4 to C15 has a data, it indicates Run-time error 91: Which says "Object variable or With block variable not set". If there are blank rows, there will be no problem.

Thanks,
Nhinx