VBA for hide automatically rows with empty cells

Question

Hi,

I'm trying to make a macro on vba (I'm a begginner) to hide automatically rows when the cells on column D are empty. The content of these cells is linked to other cells in the other sheet, so at any time may have content. Ie, are empty but if you put data in another sheet will no longer be empty and I would like to pass them visible automatically when this happened.

Anybody can help me?

Best regards

Answer 1

Hi,

So you have a simple link to another worksheet that returns zero of the cell is empty and you want to hide all rows that have a zero, is that correct? Yes, then try this code.

Private Sub Worksheet_Calculate()

Dim c As Range

For Each c In Range("D3:D100")

    If c.Value = 0 Then

        c.EntireRow.Hidden = True

    Else

        c.EntireRow.Hidden = False

    End If

Next

End Sub

Answer 2

I'm sorry but the macro doesn't work. I introduced the function "SUM" to a column (other one, not the D column) and the excel crash. This always happens every time I put the signal = "something" (content, function or operation) in any cell. I think that the macro is incompatible with something...

Answer 3

Thank you very much for your help Mike. It results perfectly now :)

Best regards

Answer 4

Thank you Mike for the fast answer. The code doesn't hide the rows that I want. The content of the cells will be text, not numerical. It shows zero because the link to other cell. And it hide row 1 and I need it. The ideal is to hide in a determinate range, like row 3 to row 100.

Answer 5

Hi,

Right click the sheet tab of the sheet where you want to hide the rows and view code. Paste the code below in on the right.

Note that this doesn't hide all the rows on the sheet only those in the used range. Close VB editor and change a cell on the other sheet and the code will execute.

Private Sub Worksheet_Calculate()

Dim c As Range, LastRow As Long

LastRow = Cells.Find(What:="*", After:=[A1], _

SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious).Row

For Each c In Range("D1:D" & LastRow)

    If Len(c.Value) > 0 Then

        c.EntireRow.Hidden = False

    Else

        c.EntireRow.Hidden = True

    End If

Next

End Sub