CODEWITHSUNDEEP
vbaexcel
Ans
Ans

Reputation: 1234

Excel vba can't hide row

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.

Upvotes: 0

Views: 334

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

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

Upvotes: 1

Related Questions