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]

Not sure I understand what you mean by "show all potential input cells", but here's a hide macro and an unhide macro you can try.

Sub HideEmptyDataRows()
Dim R As Range, Rw As Range
Set R = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("D9:M79"))
Application.ScreenUpdating = False
For Each Rw In R.Rows
    If Application.CountA(Rw) = 0 Then Rw.EntireRow.Hidden = True
Next Rw
Application.ScreenUpdating = True
End Sub
Sub UnhideEmtyDataRows()
Dim R As Range, Rw As Range
Set R = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("D9:M79"))
Application.ScreenUpdating = False
For Each Rw In R.Rows
    If Rw.EntireRow.Hidden = True Then Rw.EntireRow.Hidden = False
Next Rw
Application.ScreenUpdating = True
End Sub

 

[ExcelBeginner34]

Thanks Joe, sorry by this I just mean to show the various cells that they can input the costs so the cells in columns D to M.

I have copied and pasted the code above into the VB. When I run the hide HideEmptyDataRows macro I am getting an error "Run-time error '1004': Unable to set the Hidden property of the Range class. When I hit debug the following is highlighted in yellow

Rw.EntireRow.Hidden = True

Any thoughts on what might be the problem or how I fix this so it runs?

Best regards

Iain

 

[JoeMo]

Is the sheet your data are on the active sheet when you run the code?
Is the sheet your data are on protected?
When you get the error click Debug, then ctrl+g, then in the Immediate Window type: ?Rw.Address and press enter. What do you get?

 

[ExcelBeginner34]

Hi Joe,

The sheet was protected. I've run it again now and it's working perfectly. Thanks for your help.

Iain

 

[JoeMo]

Hi Joe,

The sheet was protected. I've run it again now and it's working perfectly. Thanks for your help.

Iain

You are welcome - thanks for the reply. Here's a modification that will do what you want w/o you having to unprotect the sheet first. Substitute your password where my name is shown in bold red font.

Sub HideEmptyDataRows()
Dim R As Range, Rw As Range
ActiveSheet.Protect "joe", userinterfaceonly:=True
Set R = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("D9:M79"))
Application.ScreenUpdating = False
For Each Rw In R.Rows
    If Application.CountA(Rw) = 0 Then Rw.EntireRow.Hidden = True
Next Rw
Application.ScreenUpdating = True
End Sub
Sub UnhideEmtyDataRows()
Dim R As Range, Rw As Range
ActiveSheet.Protect "joe", userinterfaceonly:=True
Set R = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("D9:M79"))
Application.ScreenUpdating = False
For Each Rw In R.Rows
    If Rw.EntireRow.Hidden = True Then Rw.EntireRow.Hidden = False
Next Rw
Application.ScreenUpdating = True
End Sub

 

[ExcelBeginner34]

Ideal thanks

 

[ExcelBeginner34]

Sorry Joe, just realised there isn't actually a password on it. I just clock unprotect and then click ok. How would that change the code?

 

[ExcelBeginner34]

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

 

[JoeMo]

Sorry Joe, just realised there isn't actually a password on it. I just clock unprotect and then click ok. How would that change the code?

Remove the password to just leave the two quote marks with no space between them like this:

ActiveSheet.Protect "", userinterfaceonly:=True