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

#### Alisonh

##### New Member
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.

Alison

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
What columns hold the sub-total and group headings? What are those headings?
What is the current formula that is insufficient?

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:

Replies
17
Views
349
Replies
2
Views
248
Replies
3
Views
384
Replies
2
Views
276
Replies
2
Views
145

1,216,297
Messages
6,129,955
Members
449,545
Latest member
SURY

### 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.

### Which adblocker are you using?

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

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