Hiding Blank Rows In Excel

[ExcelBeginner34]

Hi, first time poster and as you can tell from my username a bit of a novice. Hoping to get some help with a project I am working on. I have created a table as a template to be used by my colleagues. This will be the base template for them to input data for various different commercial properties that we manage. The table effectively shows costs for the running of the building under various headings security, cleaning, electricity etc. There are then 10 columns (D - M) in which the user can enter costs. These are then totalled in the 11th column (N). The data that I want to be able to work with is in D9:79 through to M79.

Due to the fact every property is different there may be times when one cost heading e.g cleaning might have no entries at all. There may also be times when there will be a figure in only one of the cells in a row. It is only the rows with no cells filled at all that I want to hide. If possible I want to have two options:

1) a macro to show all the potential input cells
2) a macro to use once the user has input the data they need and wants to hide all the rows with no contents to print a report for a client.

I also want to have the ability for someone, once they have run the macro to hide the empty cells, to revert back to being able to see all the potential input cells in case they make a mistake and want to correct this. I have shown below an example of what to try and help explain. So in this example, once the data was input I would only want to hide the management fee row. But then if I realised that there should actually have been a figure in say Sch10 I would want to run something to unhide this row, allow me to enter it and then run the macro again to hide any other blank rows in the bigger table.

Any help would be great and I hope the above explanation makes sense!!


[TABLE="width: 500"]
<tbody>[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]Cost Code[/TD]
[TD]Sch1[/TD]
[TD]Sch2[/TD]
[TD]Sch3[/TD]
[TD]Sch4[/TD]
[TD]Sch5[/TD]
[TD]Sch6[/TD]
[TD]Sch7[/TD]
[TD]Sch8[/TD]
[TD]Sch9[/TD]
[TD]Sch10[/TD]
[TD]Total Cost[/TD]
[/TR]
[TR]
[TD]Management Fee[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]£0[/TD]
[/TR]
[TR]
[TD]Cleaning[/TD]
[TD]£1000[/TD]
[TD]£1000[/TD]
[TD]£1000[/TD]
[TD]£1000[/TD]
[TD]£1000[/TD]
[TD]£1000[/TD]
[TD]£1000[/TD]
[TD]£1000[/TD]
[TD]£1000[/TD]
[TD]£1000[/TD]
[TD]£10000[/TD]
[/TR]
[TR]
[TD]Security[/TD]
[TD]£500[/TD]
[TD][/TD]
[TD][/TD]
[TD]£1000[/TD]
[TD]£1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]£4000[/TD]
[TD]£6500[/TD]
[/TR]
[TR]
[TD]Electricity[/TD]
[TD][/TD]
[TD][/TD]
[TD]£10000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]£10000[/TD]
[/TR]
[TR]
[TD]Gas[/TD]
[TD]£1000[/TD]
[TD]£1000[/TD]
[TD]£1000[/TD]
[TD]£1000[/TD]
[TD]£1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]£5000[/TD]
[/TR]
</tbody>[/TABLE]

 

[ExcelBeginner34]

Thanks - how do I separate the row? Would it be “C14:C22, F14:F22, I14:F14, C25:C30, F25:F30 and so on?

 

[JoeMo]

Use the Union and Intersect functions. For example, Set Rws = Union(Rows("14:22"),Rows("25:30")). Set R =Intersect(Columns("C"),Rws) is a range that contains two areas: C14:C22 and C25:C30.

 

[ExcelBeginner34]

Thanks - I have added the line below to cover off all the rows that I need to be hidden if there is a £0 entry. I am planning on repeating this line another twice for the Columns F and I. I am getting an error message saying Compile Error: Expected End of Statement with the R after Set highlighted. Any idea how I solve that?

Set Rws = Union(Rows("14:22"),Rows("25:30"),Rows("33:45"),Rows("48:65"),Rows("73:77"),Rows("80"),Rows("83:87")). Set R =Intersect(Columns("C"),Rws)

 

[JoeMo]

That goes on a separate line.

 

[ExcelBeginner34]

Got it thanks. Working now

 

[ExcelBeginner34]

Sorry another question. I am on to my final tab and want to hide columns on this one too. I have used the same code as provided previoulsy and updated the range to I10:AB12. When I run the code it hides all the columns which have empty cells in the range, however, there are some cells which have a % and a £0.00 which are being calculated by a formula. Does the code not work with formulas like this or the % and £ formatting applied to the cells?

 

[JoeMo]

A cell cannot be empty if it contains a formula. If it is empty you can format it anyway you want and it will remain empty.

 

[ExcelBeginner34]

Is it possible to change the code so that it hides any empty cells and cells with a value of less than £1 or 1%?

 

[JoeMo]

Is it possible to change the code so that it hides any empty cells and cells with a value of less than £1 or 1%?

Yes, just loop through the cells to see if they all meet those conditions.

 

[ExcelBeginner34]

Great worked a treat. Thanks again - hopefully no more questions!!! Getting quite into this coding. Think I’ll buy a book and try and teach myself some more!