Hide each row in a range with a 0 sum.

[racastor]

I'm attempting to look at a user defined range to see which rows sum to zero and then hide those rows. It hides the first row, but doesn't move on to the next rows. Where did I go wrong? Using Excel 2016.

Dim rng As Range
Dim row As Range
Dim cell As Range
Dim FRow As Integer
Dim LRow As Integer


FRow = InputBox("Enter the first row of the reconciliation.")
LRow = InputBox("Enter the last row of the reconciliation.")


Set rng = Range(Cells(FRow, 4), Cells(LRow, 9))


For Each row In rng.Rows
  
    If WorksheetFunction.Sum(row) = 0 Then
    Selection.EntireRow.Hidden = True
    Else
    End If
Next row

 

[Joe4]

You are telling to hide the selected row. Since your code isn't selecting anything, it is always hiding whatever row was selected when you started.

Change this row:

    Selection.EntireRow.Hidden = True

to this:

    Rows(row).Hidden = True

 

[racastor]

I am now getting a Type Mismatch at that line.

 

[Joe4]

Ah, yes. That is because you have defined "row" as a range. So we need to take the row number of that.
That would look like:

row.row

However, that is bad form. You should avoid using reserved words like "row" as the name of your variables. Doing so can cause ambiguity, unexpected behavior, and errors in your code.

Try this:

Dim rng As Range
Dim rw As Range
Dim cell As Range
Dim FRow As Integer
Dim LRow As Integer


FRow = InputBox("Enter the first row of the reconciliation.")
LRow = InputBox("Enter the last row of the reconciliation.")


Set rng = Range(Cells(FRow, 4), Cells(LRow, 9))


For Each rw In rng.Rows
  
    If WorksheetFunction.Sum(rw) = 0 Then
        Rows(rw.row).Hidden = True
    End If
Next rw

 

[racastor]

Thanks a lot. Avoiding reserved words seems like a good Best Practice I'll keep in mind.

 

[Joe4]

You are welcome!

Avoiding reserved words seems like a good Best Practice I'll keep in mind.

Yes. If I am ever not sure if a word is reserved or not, I will just prefix it with "My", just to be safe.