Hide rows that contain all 0 but not too apply to all rows only certain rows

Alisonh

New Member
Joined
Feb 21, 2019
Messages
3
Hi

I have a spreadsheet that has 56 columns from A to BD with numerical data contained in columns C to AD

The spreadsheet also has 197 rows. The rows are grouped by headings, which then detail costs on each line within those headings and then has sub totals for each group.

I would like to hide the rows that contain all 0's from columns C to BD except for those rows that contain the names of the heading sections and sub total sections.

The formula's I have used previously hide all the rows including the sub totals and group headings which I need to remain.

Thanks in advance

Alison
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,210
What columns hold the sub-total and group headings? What are those headings?
What is the current formula that is insufficient?
 

Alisonh

New Member
Joined
Feb 21, 2019
Messages
3
Hi

All of columns from C:AD have sub totals in them and I need all the columns to be visible.

The code was given to me by a colleague as I am new to VBA

Sub HideAllZeroesInColumnsCtoBD()
Dim R As Long, C As Long, LastRow As Long, UnusedCol As Long, Zeroes As Long
Dim Data As Variant, Result As Variant
LastRow = Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row
UnusedCol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column + 1
Data = Range("c6:bd" & LastRow)
ReDim Result(1 To UBound(Data), 1 To 1)
For R = 1 To UBound(Data)
Zeroes = 0
For C = 1 To 13
If Data(R, C) = 0 Then
Zeroes = Zeroes + 1
Else
Exit For
End If
Next
If Zeroes = 13 Then Result(R, 1) = "X"
Next
Cells(2, UnusedCol).Resize(UBound(Result)) = Result
On Error Resume Next
Columns(UnusedCol).SpecialCells(xlConstants).EntireRow.Hidden = True
On Error GoTo 0
Columns(UnusedCol).Clear
End Sub
Code:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,626
Messages
5,838,438
Members
430,548
Latest member
hh_dh2001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top