Reputation: 780
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
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
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