Marco to hide blank rows

zedordead · #1 (**permalink**) 12-04-09, 06:42

Hi there,

I have an excel sheet with Matrix at the top and below a load of code that is generated by looking at the values in the matrix. The problem I have is that when certain matrix values are typed in there are a load of blank rows appear like this:

Code:

Matrix:

    1    1    1
    2    1    3
    5    3    3

Code:

    x=y

    y=d
    g=y
    t=p


    o=g

The cells in between each code line contain formula but are blank when a cetain matrix combination is written.

I would like to have a MArco to hide these cells so it looks a bit 'prettier' like so:

Code:

Matrix:

    1    1    1
    2    1    3
    5    3    3

Code:

    x=y
    y=d
    g=y
    t=p
    o=g

Any ideas would be greatfully recieved!

Thanks

Colin Legg · #2 (**permalink**) 12-04-09, 11:48

Conceptually the approach would be:

Code:

Sub foo()
    Dim rngCell As Range
    
    For Each rngCell In Range("A10:A20")
        rngCell.EntireRow.Hidden = IsEmpty(rngCell)
    Next rngCell
    
End Sub

A10:A20 would be the range containing the "x=y" etc.. values. Presumably this would have to be determined at runtime - I can't suggest the code to do that because there isn't enough information in your post. I guess that's something you could have a go at?

Hope that helps...

zedordead · #3 (**permalink**) 12-04-09, 11:57

HI Colin,

Thanks for yor reply. Sorry about the bad explaination!

That would work if there was nothing in the cells. But there are formulae in the blank cells that appear when the Matrix is changed. Any idea how to hide them when they are blank.

The range where the code us in is B12:B34... so if B20 is blank for a particular Matrix I would like to hide the whole of row 20.

Any ideas?

Thanks,
Connor

Colin Legg · #4 (**permalink**) 12-04-09, 12:10

Hi Connor,

Ah okay... I didn't know there are formulas in B12:B34... can you give an example of one of the formulas? Are they returning empty strings? ie. =IF(condition,true result,"") or something similar?

zedordead · #5 (**permalink**) 12-04-09, 12:12

Hi,

Yes exactly, the formulae are all in the style =IF(condition,true result,"") .

Connor

Colin Legg · #6 (**permalink**) 12-04-09, 12:15

Code:

Sub foo()
    Dim rngCell As Range
    
    For Each rngCell In Range("B12:B34")
        rngCell.EntireRow.Hidden = (LenB(rngCell.Value) = 0)
    Next rngCell
    
End Sub

zedordead · #7 (**permalink**) 12-04-09, 12:18

Worked like a charm!

Thankyou!

zedordead · #8 (**permalink**) 12-04-09, 12:43

One last question...

How do I get this Macro to run when a change is made in the cells C4:G6 (the matrix)?

:-)

Colin Legg · #9 (**permalink**) 12-04-09, 13:03

Hi,

Do the the matrix cells also contain formulae? (double checking this to make sure we use the correct event handler)

Also, which range of cells holds the matrix?

zedordead · #10 (**permalink**) 12-07-09, 06:06

The Matrix is in C4:G6 and do not contain any formulae.

Connor

Colin Legg · #11 (**permalink**) 12-07-09, 07:35

In that case we need the Worksheet_Change event handler.

Right click on the worksheet tab and choose View Code. This will open up the worksheet's class module in the VBE. Add in this code:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Const strMATRIX As String = "C4:G6", strFORMULAS As String = "B12:B34"
    Dim rngCell As Range
    
    On Error GoTo ErrorHandler
    
    'was a cell in the matrix changed?
    If Not Intersect(Target, Range(strMATRIX)) Is Nothing Then
        Application.ScreenUpdating = False
        
        'calculate the formula range in case user has calcs set to manual
        Range(strFORMULAS).Calculate
        
        For Each rngCell In Range(strFORMULAS).Cells
            rngCell.EntireRow.Hidden = (LenB(rngCell.Value) = 0)
        Next rngCell
    End If
    
CleanUp:
    Application.ScreenUpdating = True
    Exit Sub

ErrorHandler:
    MsgBox "error number: " & Err.Number & vbNewLine & "error message: " & Err.Description
    Resume CleanUp
End Sub

Rather than using string constants for the two ranges you could use named ranges.

Hope that helps...

Internet Services

Web Development

Digital Marketing

Consumer Tech