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]

Adjusted, 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
            On Error Resume Next
            rngHide.EntireRow.Hidden = True
            On Error GoTo 0
            .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 is fantastic. That solved my problem. A million thanks to you.

Regards,
Nhinx

 

[JackDanIce]

You're welcome Nhinx, thank you for the feedback