Hide/Unhide Merged Cells using VBA

[CopperWire]

I need to hide and unhide rows based on criteria from a drop down box. I recorded a macro to do this, however, since the first column in these rows are merged, the macro hides all the rows that are merged. The only way around it that I've found in to unmerged the first column hide/unhide the rows and then merge is again.

Does anyone have an easier way to do this? My code is below:

Sub OhWorkPlan()

ActiveWindow.SmallScroll Down:=24
Range("A27:A66&quot.Select
ActiveWindow.SmallScroll Down:=34
Range("A27:A66,A75:A92,A75:A92&quot.Select
Range("A75&quot.Activate
ActiveWindow.SmallScroll Down:=-43
With Selection
.WrapText = False
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 20
Range("28:34,41:64&quot.Select
Range("A41&quot.Activate
ActiveWindow.SmallScroll Down:=16
Range("28:34,41:64,82:85,90:90&quot.Select
Range("A90&quot.Activate
Selection.EntireRow.Hidden = True
Range("A27:A66&quot.Select
With Selection
.WrapText = False
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("A75:A89&quot.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 90
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
End Sub

Thanks in advance for your help!

 

[SkipVought]

Hi,

1. Merge really screws things up. duh -- you figgered that one out!

2. Your code needs some cleaning up.

Sub HideRows()
'store merge cell layout
    Set wsThis = ActiveSheet
    With wsThis.UsedRange
        r1 = .Row
        r2 = r1 + .Rows.Count - 1
        c1 = .Column
        c2 = c1 + .Columns.Count - 1
    End With
    wsThis.Copy Before:=Sheets(1)
    Set wsCopy = ActiveSheet
    With wsThis
        .Cells.UnMerge
'        For Each r In RangeList
'            Range(r.Value).EntireRow.Hidden = True
'        Next
    End With
    wsCopy.UsedRange.Columns.Copy
    wsThis.UsedRange.Columns.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.DisplayAlerts = False
    wsCopy.Delete
    Application.DisplayAlerts = True
End Sub

where RangeList is the list of ranges to hide



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[CopperWire]

Thanks for your help!