Need code to compare cell values in a column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am looking for some code that will allow me to check the cell values in a
column, if all the values in the column are the same I want to hide the
column, if any single value in the column is dissimilar I want the column to
stay visible. The row count for the columns is not static and can change
from sheet to sheet. Thanks for any assistance.
 
Hi

try the code

Code:
--------------------

Sub test()
Dim myR As Range, myRange As Range, myCol As Integer, x
On Error GoTo Last
Set myR = Application.InputBox("select column haeding that you want to test", Type:=8)
Application.ScreenUpdating = False
With myR
myCol = .Column
Set myRange = Range(myR, Cells(65536, myCol).End(xlUp))
End With
On Error Resume Next
myRange.AdvancedFilter xlFilterInPlace, unique:=True
x = myRange.SpecialCells(xlCellTypeVisible).Count
If x <= 2 Then
ActiveSheet.ShowAllData
myRange.EntireColumn.Hidden = True
Else
ActiveSheet.ShowAllData
myRange.EntireColumn.Hidden = False
End If
Last:
Application.ScreenUpdating = True
End Sub
 
Another method:

sub test()
Set rng = Range("H4")
Set myRng = Range(rng, rng.End(xlDown))

If WorksheetFunction.CountIf(myRng, rng.Value) = myRng.Rows.Count
Then
rng.EntireColumn.Hidden = True
Else
rng.EntireColumn.Hidden = False
End If
end sub

Replace the "H4" with your first cell in the range.

Mangesh
 
Back
Top