How can I Hide Excel rows if SUM of contents = zero and skip rows containing any text?

Question

In line with a previous question I asked I am trying to hide rows that contain zeros, but skip over rows that contain any text in any one or more cells. I received excellent help previously skipping blank rows, and was hoping for more help now. I've searched through the internet on every forum I can find and have found nothing that does what I need it to. There are two codes on that hides and an exact copy of that one but with hide set to false. Here is the one that Hides.

Sub HideRows()
Dim R As Long
Dim Rng As Range
    If Selection.Rows.Count > 1 Then
        Set Rng = Selection
    Else
        Set Rng = ActiveSheet.UsedRange
    End If      
    For R = 1 To Rng.Rows.Count
    Set myRange = Range(Rng(R, 2), Rng(R, Rng.Columns.Count))
      If Application.CountBlank(myRange) <> myRange.Cells.Count And IsNumeric(myRange(Row)) = False Then
        If Application.Sum(myRange) = 0 Then
        Rng.Rows(R).Hidden = True
        End If
      End If
    Next R        
End Sub

By the way I know that the IsNumeric(myRange(Row)) = False really should probably be = True, but for some reason on one of my worksheets this setup works and if I change to True it pretty much does nothing.

Thanks in Advance for any help.

Answer 1

The cause of your problems is And IsNumeric(myRange(Row)) = False

1. Row is undefined and never set. So it will have the default value of 0. Therefore (since myRange is defined starting at column 2) myRange(Row) refers to the single cell in column A on the row myRange refers to.
2. If you drop the (Row) bit, IsNumeric will always return FALSE

Also, Set myRange = Range(Rng(R, 2), refers to Row R of the used range, offset one column to the right

Conclusion:

1. Assuming you want to test all cells, change to `Set myRange = Range(Rng(R, 1), Rng(R, Rng.Columns.Count))
2. To correctly test for no non-numeric cells use
   If Application.Count(myRange) > 0 And _
Application.CountBlank(myRange) + _ Application.Count(myRange) = myRange.Cells.Count Then

By the way, its good practice DIM all you variables. This would have identified the issue with (Row). If you add Option Explicit to the top of your module this will become manditory.