VBA Hide/Unhide Rows

[surkdidat]

In Column A I have the name of a staff member. This is a merged cell spread out over many rows such as Peter in one merged cell from Rows 7:11, Andy in Rows 13:17, Peter in 19:23 etc.

Each staff member will have a button on the spreadsheet. When it is pressed it needs to hide all rows without that name in.

So, if the spreadsheet is fully displayed and I select "Peter" it should then hide every row (between 7 and 100 for now) that contains any other name bar Peter (so in effect it will only show Peter's records. (Rows 7:11, Rows 19:23 in example above will show, and rows 19:223 will be hidden).

There will be a separate button to unhide all records. (I appreciate this can be done by a filter, but to limit access so if possible this will be a locked and protected sheet without autofilter being available)

It will be a bonus if you do not have to "Show All" before selecting another name. So, if I had Peters records up, and then clicked on Andys it will do this, without having to click on a separate button to show all first

Thanks in advance!

 

[JLGWhiz]

VBA does not like merged cells. It is difficult to deal with them when attempting to copy,/paste or hide/unhide rows and columns. The format of the merged cell is not dealt with in the VBA application.

 

[Yongle]

This works for me
- but may not work if there are any other merged cells in the sheet

Test in a copy of your workbook

Suggested solution which works for me
- Use form control Buttons (not active-x command buttons)
- Ensure each Button text is IDENTICAL to each name in column A
- Assign ShowUserRows to each button

Sub ShowUserRows()
    Dim cel As Range, rng As Range, usedRng As Range, aName As String
    aName = ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text

    Set usedRng = ActiveSheet.UsedRange
    Set rng = Range("A7", Range("A" & usedRng(usedRng.Rows.Count).Row))
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each cel In rng
        cel.EntireRow.Hidden = True
        cel.MergeArea.EntireRow.Hidden = True
        If cel.MergeArea.Cells(1, 1).Value = aName Then cel.MergeArea.EntireRow.Hidden = False
    Next cel
    On Error GoTo 0
End Sub

There will be a separate button to unhide all records

Work it out for yourself from the code provided