Hide rows until data input in Excel

Question

Hi, On the sheet below, every cell to the right of COST has a formula that calculates the appropriate tax. I have dragged row 3 down and hidden it using a format of ;;;. But what I would like is for those rows to be invisible until something is entered in column E. Using ;;; is not a long term solution. If there wa an error then ISERROR would work, but there is no error message. What can I use to keep these rows hidden until data is entered in Column E that would trigger the calculations an expose the data? By the way I use Office for Mac 2021.

Answer 1

You might change formulas

=expression

to

=IF(E2="", "", expression)

Another option is to delete all unused rows, then convert the range (A1:L3 in your screenshot) into a table.

Pressing Tab in the last cell of the table will create a new row, and copy the formulas to the new row.

Answer 2

Hi, Tesnobay

Thank you for using Microsoft products and posting them to the community.

I'm not sure I understand what you're saying, but my understanding is that if there is no data in column E, the row is hidden. If there is data, it is displayed.

I am using VBA to solve the problem with the code:

Private Sub Worksheet_Change(ByVal Target As Range) 

    Dim cell As Range 

    If Not Intersect(Target, Me.Columns("E")) Is Nothing Then 

        For Each cell In Intersect(Target, Me.Columns("E")) 

            If cell.Value <> "" Then 

                Me.Rows(cell.Row).Hidden = False 

            Else 

                Me.Rows(cell.Row).Hidden = True 

            End If 

        Next cell 

    End If 

End Sub 

Open the VBA editor via Option+F11, find your workbook in the Project Explorer on the left, it should be Sales Date, and paste the above code.

Close the VBA editor and return to Excel.

What this code does is that when a cell in column E changes, it displays the corresponding row if the cell is not empty, or hides the row if it is empty.

If you want to redisplay, for example, row 6 is hidden, you can select rows 5,7 and then right click and choose not to hide and row 6 will be redisplayed.

I hope the above information can help you. Feel free to send a message if you need further help.

Best wishes

Aiden.C - MSFT |Microsoft Community Support Specialist

Answer 3

The table works great thank you very much. I knew I knew that buy my mind couldn't couldn't bring it forward.

Thanks again,

Tom

Answer 4

thank you very much for your suggestion, sorry if I was not clear enough, my bad!

Anyway using a table was the best and quite frankly easiest solution, I am disappointed in myself for not thinking of it!

Thanks again for your help.

Tom