unhide row if above row contains data in a cell

  • Hi all,


    Is it possible to unhide a row if the above one in a cell has data in it?
    Normaly we hide those rows manually if a cell in column D has no value in it, but it is really anoying doing this by hand everytime.


    Greetings.

  • Re: unhide row if above row contains data in a cell


    Try this

    Code
    1. Sub hide_empty()
    2. Columns("D").Rows.Hidden = False
    3. lr = Cells(Rows.Count, "D").End(3).Row
    4. Range("D1:D" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
    5. End Sub


    To alternate between hide and Unhide Use this One

    Code
    1. Sub hide_empty1()
    2. lr = Cells(Rows.Count, "D").End(3).Row
    3. Range("D1:D" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = Not (Range("D1:D" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden)
    4. End Sub
  • Re: unhide row if above row contains data in a cell


    Hi Cytop,


    it is populated manually. There are some formulas in the same row, but these only calculate what is inputed in a cell in column E (it is not column D as I said it wrong in the other post).


    Greetings.

  • Re: unhide row if above row contains data in a cell


    Check when the cell above the cell/row in Col E is changed...



    So you change something manually in Col E - the row below will be unhidden if the cell is not blank (Of course you'll have to hide the rows first).
    There seems to be sometihng left unsaid here, I'm not sure this is what you want, though.

  • Re: unhide row if above row contains data in a cell


    Hi Cytop,
    I will try it in a few moments.
    you are right, there are a few more things to be mentioned. The one who designed the workbook, tried to format it some how and use some of the rows and columns to use them as spacers.
    There is a row used as space between to cells with calculation (in column E there are some cells that need to be filled in manually and then a formula a few columns to the right calculate.
    the idea is to calculate for some product used. So, this person created a few more so they could be used if there where more products (normaly between 1 and 4, but it also could be 20 or more)
    silly problem came up that there is a calculation field in column R that should stay visible (just noticed it now).
    The rows from E20 till E24 should be hiden and then only be visible if one got value. The cell E18 is the default one being visible all the time and then E20 till E24.
    If cell E18 gets a value, then E20 should become visible, if E20 got a value, then E22 should become visible (the jump in rows is because of the spacing row)


    It would be great if the macro could be used for some rages that could be added more if needed.


    Greetings.

  • Re: unhide row if above row contains data in a cell


    Try this then.


    Change the reference to E14:E24 to refer to whatever range of rows this is to work for. Assuming data entry is on Even numbered rows as you described. If on Odd Rows then change to

    Code
    1. If Target.Row Mod 2 = 1 then


    Can't do anything about the "calculation field in column R that should stay visible" - the row is either hidden, or it is not.


    EDIT - Just realised, if you hide a row then the next spacing row will cause 2 blank rows to be displayed. Perhaps you need

    Code
    1. Target.Offset(2).Resize(2).EntireRow.Hidden = (Target.Value = vbNullString)
  • Re: unhide row if above row contains data in a cell


    Hi Cytop,


    Than one really works and the above code too :-)
    I saw the jump and when I adjusted it to offset 2, it worked really great :-)


    Thanks a lot, you always are great with solutions :-)

  • Hi Megatronixs and Cytop,


    This thread was what I was searching for. I've implemented Cytop's code in my excel workbook and it works like a charm.


    At the moment this thread is more than 365 days old, so I hope you will still read this.


    My workbook has multiple sheets. Now I have implemented Cytop's code in a module so that every worksheet can call the module for hiding/unhiding the row's. Then if something changes I do not need to change every worksheet code, but only the module code.


    I've used the code below to call the module:

    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Not Intersect(Target, Range("C:C")) Is Nothing Then
    3. Call HideUnhide3
    4. End If
    5. End Sub

    It calls the module, but then Cytop's code doesn't work in the module. It only works in the sheet.


    What needs to be changed for this to work?



    Kind regards,

  • royUK

    Closed the thread.