Need code to compare cell values in a column

[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.

 

[jindon]

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

 

[mangesh_yadav]

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