CODEWITHSUNDEEP
excelvba
GoldFusion
GoldFusion

Reputation: 149

Hide/Unhide merged cells in excel

So on excel I have 2 worksheets one called Setup and the other called Summary. The user is asked to fill out questionnaire from the "Setup" worksheet. Information filled out in the setup page copies over to the Summary page. Below are 2 pictures showing the Setup page and Summary page.

Setup Page

![enter image description here

Summary page

enter image description here

So the issue I am having is that I am trying to automate the hide/unhide feature using vba and its not working for merged cells. I want the Summary page to hide Rows 33 to 35 if the setup page is left blank and unhide if something is written on the Setup page. Here is the code I have tried below and got this run-time error.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

     With Sheets("Setup")
            If Range("M29").value = False Then
            Sheets("SUMMARY").Range("B33:B35").Hidden = False
            Else
            Sheets("SUMMARY").Range("B33:B35").Hidden = True
            End If
     End With
   End Sub

enter image description here

Upvotes: 1

Views: 1156

Answers (1)

BigBen
BigBen

Reputation: 49998

Instead of Range, use Rows.

Sheets("SUMMARY").Rows("33:35").Hidden = False 'and similarly for True

Also, note that you're not actually referencing the With. You need a period . before Range.

With Sheets("Setup")
    If .Range("M29").value = False Then

You may be looking for IsEmpty to test if that range is blank.

Also, if this code is in the "Setup" sheet code module, then you can use Me instead of Sheets("Setup").

With those edits, you might have something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If IsEmpty(Me.Range("M29").Value) Then
        Sheets("SUMMARY").Rows("33:35").Hidden = True ' shouldn't this be True, hide if empty?
    Else
        Sheets("SUMMARY").Rows("33:35").Hidden = False
    End If

End Sub

Or as a one-liner:

  Sheets("SUMMARY").Rows("33:35").Hidden = IsEmpty(Me.Range("M29").Value)

Upvotes: 4

Related Questions