Hiding zero values in multiple columns

Question

I have a macro that will hide any rows in a range that have a zero value in a particular column..

Application.ScreenUpdating = False

Dim r As Range, cell As Range

On Error GoTo ErrHandler

Set r = Range("d9:d24")

Application.ScreenUpdating = False

Application.EnableEvents = False

For Each cell In r

 If cell.Value = 0 Then

   cell.EntireRow.Hidden = True

 Else

   cell.EntireRow.Hidden = False

 End If

Next

ErrHandler:

Application.ScreenUpdating = False

Application.EnableEvents = True

'

End Sub

I would like to extend this functionality to work for any zero values over 3 collumns adjacent to each other..

Any help is as always much appreciated..

Answer 1

Hi,

i assume that your data are from A1 down and to the right

try this code...

Sub Zeros3()

Dim r, c As Long

Dim myR As Range

Application.ScreenUpdating = False

r = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious).Row

c = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _

SearchDirection:=xlPrevious).Column

For i = 1 To r

s = "'"

For j = 1 To c

If IsEmpty(Cells(i, j)) Then s = s + "#" Else s = s & Cells(i, j)

Next j

Cells(i, c + 1) = s

Next i

For i = r To 1 Step -1

Set myR = Cells.Find(What:="000", LookIn:=xlValues, LookAt:=xlPart)

If Not myR Is Nothing Then myR.EntireRow.Hidden = True

Next i

Range(Cells(1, c + 1), Cells(r, c + 1)).Clear

Application.ScreenUpdating = True

End Sub

Answer 2

Here is some non-looping code which should execute quite quickly that you can try...

Dim UnusedColumn As Long

UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _

               SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1

With Range(Cells(9, UnusedColumn), Cells(24, UnusedColumn + 2))

  .Value = Range("D9:F24").Value

  .Replace 0, "", xlWhole

  .SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

  .Clear

End With

Answer 3

If by "any zero values over 3 columns adjacent to each other" you mean any cell in that row in any column = 0, one way:

Dim rCell As Range

For Each rCell In Range("D9:D24")

With rCell.Resize(1, 3)

.EntireRow.Hidden = (.Item(1) = 0) Or (.Item(2) = 0) Or (.Item(3) = 0)

End With

Next rCell

If you mean that all three cells in the row have to be zero, replace the 'Or's with 'And's.