Macro: Hide multiple rows based on value in first row

[dropkickweasel]

Hopefully someone can help me with a problem I'm having with merged cells and a macro not getting on too well...

On a sheet titled "Data", column A has a list of reference numbers.

These are taken from column C on sheet one.

Using "if(Sheet1!C5 = "","",Sheet1!C5), Cell A5 is populated with the appropriate reference number if one has been typed
into Sheet1, else it is left blank.

Cell A5 is merged to include A5 - A10.

Cell A11 is merged to include A11 - A16.

This second cell draws the reference number from cell C6 on Sheet1.

The reason for the merge is that there are a possible of six different similar pieces of information which can be stored in later columns all referring to the same person (and therefore the same reference number).

There are a maximum possible 32 different reference numbers on Sheet1 (C5:C36), which populate 32 different merged cells in column A of the Data sheet (A5:A196)

References will be filled from the first row downwards, but not all rows will have data in them.

What I would like is for a macro to run on Worksheet_Activate() hiding all rows which do not have a reference in them.

I have tried this by using:

Private Sub Worksheet_Activate()


Application.ScreenUpdating = False


ActiveSheet.Unprotect 
    For Each Cell In Range("ReferenceNumber")
                               'ReferenceNumber is the named range A5:A196 on the Data sheet
        If Application.WorksheetFunction.IsNumber(Cell) Then
            Cell.EntireRow.Hidden = False
        Else
            Cell.EntireRow.Hidden = True
        End If
    Next Cell
ActiveSheet.Protect 


End Sub

Unfortunately when it runs, it recognises that A5 has a reference number, but A6-A10 don't and so hides those five rows and the relevant data in the later columns.

Is there a way to make VBA recognise that the reference typed in cell C5 of Sheet1 applies to ALL of cells A5-A10 of DataSheet and doesn't hide A6-A10 believing them to be blank?

This project is my first foray into VBA, so please be simple with your explanations, so that I can improve and solve my own problems next time...

Many thanks in advance for any support, advice or code that you may offer :]

 

[JLGWhiz]

I think your choices are to either unmerge the cell and put the sheet 1 reference in each of the six cells of the unmerged range, or to use a different criteria for determining which rows to hide.

But then, I have a thing about merged cells, so I might be a bit biased in my opinion.

 

[dropkickweasel]

Thanks for taking the time to look at my issue and post a suggestion :]

I think your choices are to either unmerge the cell and put the sheet 1 reference in each of the six cells of the unmerged range, or to use a different criteria for determining which rows to hide.

But then, I have a thing about merged cells, so I might be a bit biased in my opinion.

I feared that may be the case...

From an aesthetics standpoint, the sheet would look horrible with the unmerge and repeat the reference six times option...

Is there any way of programming "if A5 is not blank, then rows 5-10 remain visible, if A5 is blank, then rows 5 - 10 are hidden", do you think?

Or "if the first row of every six is blank, starting from A5, then hide the next six consecutive rows, else leave them visible"?

I don't know enough about VBA syntax to work this out for myself, sorry...

As always, all and any help gratefully received.

 

[JLGWhiz]

Try this:

Sub hideEm()
Dim sh As Worksheet, lr As Long
Set sh = Sheets(1)
sh.Rows.Hidden = False
lr = sh.Cells.Find("*", sh.Range("A1"), xlFormulas, xlPart, xlByRows, xlPrevious).Row
    For i = 5 To lr Step 6
        If sh.Cells(i, 1) = "" Then
            sh.Cells(i, 1).Resize(6, 1).EntireRow.Hidden = True
        End If
    Next
End Sub

 

[dropkickweasel]

Thanks for that. I copied the code into VBA and tried to run it as is and ten again having changed the range ("A1") to "ReferenceNumber", but sadly it didn't work... Any thoughts?

Ideally I'd be looking to have the macro run automatically on Worksheet_Activate.

Thanks for the input so far :]

 

[dropkickweasel]

I think I've worked out the right way to phrase the problem in English, but need help putting this into the relevant language that VBA can understand... anyone able to help, please?

Sub Worksheet_Activate()
UserDefinedRange: A5:A196 “ReferenceNumber”
Look at First Cell in Range
If Cell <>0 Then do nothing [Cell is populated with formula =if(Sheet1!C5 = “”,””,Sheet1!C5)]
Else if Cell is blank [“” as result of if formula] Then hide current row and next five rows
Move to check every 6^th cell in range (A5, A11, A17, A23…A191)
End If
End Sub

Is this a logic sequence that is possible to program in VBA?

Many thanks for any input anyone might be able to give.

 

[dropkickweasel]

Problem Solved!

Thanks for the help, I managed it by creating a new non-contiguous named range (DataHide) using just the first cell of the merged cells and it seems to work fine:

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
ActiveSheet.Unprotect
For Each Cell In Range("DataHide")
        If Application.WorksheetFunction.IsNumber(Cell) Then
            Cell.Resize(6, 1).EntireRow.Hidden = False
        Else
            Cell.Resize(6, 1).EntireRow.Hidden = True
        End If
    Next Cell
ActiveSheet.Protect
End Sub

Many thanks for the input :]