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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,785
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:
 

Watch MrExcel Video

Forum statistics

Threads
1,109,433
Messages
5,528,738
Members
409,831
Latest member
KT50

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top