Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2009
    Posts
    19

    Unanswered: Marco to hide blank rows

    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

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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...

  3. #3
    Join Date
    Sep 2009
    Posts
    19
    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

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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?

  5. #5
    Join Date
    Sep 2009
    Posts
    19
    Hi,

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

    Connor

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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

  7. #7
    Join Date
    Sep 2009
    Posts
    19
    Worked like a charm!

    Thankyou!

  8. #8
    Join Date
    Sep 2009
    Posts
    19
    One last question...

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

    :-)

  9. #9
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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?
    Last edited by Colin Legg; 12-04-09 at 13:09.

  10. #10
    Join Date
    Sep 2009
    Posts
    19
    The Matrix is in C4:G6 and do not contain any formulae.

    Connor

  11. #11
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •