Night Monger
Night Monger

Reputation: 780

excel show or hide rows based on condition of cell

I have an excel and i have a macro buttons. The functionalities is Simple. I have 4 products and their features. What I want to do is, Have to buttons that will show all the differences or similarities - esp since the list is huge.

So when someone clicks on show similarities, it will show only rows that have same value for example Row 2, R7,R8, R10, R11 When they click on show differences - R3, R4, R5 and so on.

This is what i tried.

Sub BtnShowdifferences_Click()
Dim R As Range("$B$2:$D$11")
For Each row In R.Rows
For Each cell in row.cells
Dim nextcell = cell + 1
If (cell.Value) == (nextcell.Value)  Then
cell.EntireRow.Hidden = True
End If
Next
Next
End Sub

How do i check the value of all the cells in the whole row (Excluding the first one).

Upvotes: 0

Views: 947

Answers (2)

A.S.H
A.S.H

Reputation: 29332

How do i check the value of all the cells in the whole row (Excluding the first one).

Here's a function to do it. It can be used normally or as a UDF as well.

Function allCellsEqual(r As Range) As Boolean
  allCellsEqual = Application.CountIf(r, r.Cells(1).Value2) = r.Cells.count
End Function

You can use it on each individual row (actual data, not entire-row), and act accordingly. If you don't want to make it a function, you can still use the logic directly in your routine:

Dim R As Range, row as Range
Set R = Range("$B$2:$D$11")  '<-- preferably qualify to sheet, i.e. sheet1.Range(...)
For Each row In R.Rows
  ' row.EntireRow.Hidden = Not allCellsEqual(row)
  ' or directly:
    row.EntireRow.Hidden = Application.CountIf(row, row.Cells(1).Value2) <> row.Cells.count
Next

This will hide rows with distinct values. To hide rows with equal values, just use = instead of <>.

Upvotes: 2

BruceWayne
BruceWayne

Reputation: 23283

You're getting there, but some syntax is off (like you just need = in VBA to compare values).

How does this work?

Sub BtnShowdifferences_Click()
Dim R As Range, row As Range
Dim iRow    As Long
Set R = Range("$B$2:$D$11")
For Each row In R.Rows
    If row.row <> R.Rows(1).row Then
        With row
            If .Cells(1) = .Cells(2) And .Cells(2) = .Cells(3) And .Cells(3) = .Cells(1) Then
                row.EntireRow.Hidden = True
            End If
        End With
    End If
Next row
End Sub

But looking at it, I'm sure there's a quicker/different Non-VBA solution. Perhaps a helper column with a filter?

Upvotes: 0

Related Questions