Excel vba can't hide row

Question

I have this code that goes through rows and checks for duplicates. If there is a duplicate if should hide the row. But somewhy it doesn't hide it.

Function sumAll()

Dim firstRow As Long
firstRow = 5
Dim lastRow As Long
lastRow = 1424
Dim aRow As Long

Dim totalDoubles As Long
totalDoubles = 0

Dim sumResult As Double
sumResult = 0
Dim previousValue As String

previousValue = -1
For aRow = firstRow To lastRow
    If Cells(aRow, 1).Value <> previousValue Then
        sumResult = sumResult + Cells(aRow, 2)
        previousValue = Cells(aRow, 1)
        Cells(aRow, 1).EntireRow.Hidden = True
    Else
        totalDoubles = totalDoubles + 1

    End If
Next aRow
sumAll = sumResult
MsgBox ("end: " & totalDoubles)

End Function

I also tried Sheets("Sheet name").Rows("5:5").EntireRow.Hidden=True but it gave no effect as well.

Declare the whole thing as Sub sumAll() ... End Sub. A function is a thing that takes parameters and returns a return value. What you're doing needs a sub. During a function execution you can not alter the worksheets / workbook. — Rik Sportel
– Rik Sportel, Commented Jul 11, 2017 at 10:55

Shai Rado · Accepted Answer · 2017-07-11 10:54:02Z

1

Try changing

Cells(aRow, 1).EntireRow.Hidden = True

to:

Rows(aRow).Hidden = True

Note: as @Rik Sportel mentioned, there's no reason for this to be a Function as you are not returning any values or objects from it, so it can be a regular Sub.

To make sure all your Cells and Rows are fully qualified with your Worksheet object, add the With statement:

With Sheets("YourSheetName")
    For aRow = firstRow To lastRow
        If .Cells(aRow, 1).Value <> previousValue Then
            sumResult = sumResult + .Cells(aRow, 2)
            previousValue = .Cells(aRow, 1)
            .Rows(aRow).Hidden = True
        Else
            totalDoubles = totalDoubles + 1
        End If
    Next aRow
End With

answered Jul 11, 2017 at 10:54

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel vba can't hide row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related