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]

 

[JoeMo]

Also - is there a way you can get it to do the same hiding exercise but for any fully empty columns?

Do you mean columns containing nothing within the range D9:M79?

 

[ExcelBeginner34]

Yes that's right.

 

[JoeMo]

That's just the vertical analog of what I gave you for hide/unhide rows. Like this:

Sub HideEmptyDataColumns()
Dim R As Range, Col As Range
ActiveSheet.Protect "", userinterfaceonly:=True
Set R = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("D9:M79"))
Application.ScreenUpdating = False
For Each Col In R.Columns
    If Application.CountA(Col) = 0 Then Col.EntireColumn.Hidden = True
Next Col
Application.ScreenUpdating = True
End Sub
Sub UnhideEmtyDataColumns()
Dim R As Range, Col As Range
ActiveSheet.Protect "", userinterfaceonly:=True
Set R = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("D9:M79"))
Application.ScreenUpdating = False
For Each Col In R.Columns
    If Col.EntireColumn.Hidden = True Then Col.EntireColumn.Hidden = False
Next Col
Application.ScreenUpdating = True
End Sub

 

[ExcelBeginner34]

Thanks - if I add this code to the code for the rows macro (you gave me earlier) in VB will this then
run straight after the rows hiding or will I need to run one and then run the other?

 

[JoeMo]

Thanks - if I add this code to the code for the rows macro (you gave me earlier) in VB will this then
run straight after the rows hiding or will I need to run one and then run the other?

Easiest way would be to call one from the other.

Say you run the rows first, then just before the rows End Sub line add this line:

Call HideEmptyDataColumns

Alternatively, you could just integrate the column code with the row code.

 

[ExcelBeginner34]

Ideal thanks for all your help -much appreciated.

 

[JoeMo]

Ideal thanks for all your help -much appreciated.

You are welcome - thanks for the reply.

 

[ExcelBeginner34]

Hi Joe, sorry another question. I am now working on the same spreadsheet but a different tab. On this one, I want to hide the rows where any of the cells in Columns C, F and I are £0. However, it would only be for rows 14 -22, 25 - 30, 33 - 45, 48 - 65, 73 - 77, 80 and 83 - 87. How would I do this?

 

[ExcelBeginner34]

Sorry i should have said i would also want to unhide the rows like the other tab you helped me with.

 

[JoeMo]

Set R to include only those rows you want to hide or unhide.