Locate Column that only has one value in excel

Hi
one of the forum gurus may give you a less clunky solution, but this seems to work
it ignores blank cells and is case sensitive - let me know if you want these settings changed

Sub FindOnes()
Dim MyRg As Range, CCol As Range, CCell As Range, MyVal, ColC As String, IsDups As Boolean
Set MyRg = ActiveSheet.UsedRange
ColC = ""
For Each CCol In MyRg.Columns
    MyVal = ""
    IsDups = True
    For Each CCell In CCol.Cells
        If MyVal = "" And CCell <> "" Then MyVal = CCell
        If CCell <> MyVal And CCell <> "" Then
            IsDups = False
            GoTo nXtC
        End If
    Next CCell
    If IsDups = True Then ColC = ColC & " " & CCol.Cells(1).Address(0, 0)
nXtC:
Next CCol
MsgBox "these cells are in columns with duplicate values" & ColC
End Sub

Hi, welcome to the forum

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

NickyC - Thats works but I want the opposite of it. I want to locate the column(s) that only has a single (distinct) value all across that column. That means that has 1 value Rob in all the cells in that column.

This formula is looking for all blank columns in the spreadsheet. I want the columns that has a single(Distinct) value in the spreadsheet.

Locate a column where the vale remains same in all cells and doesn't change. Hope this explanation helps.

Admin - I can't find the button for attaching a dummy file.

Follow my steps and your should see it.

Thanks NickyC for the great code
Another approach

** I got Firewall error scruri when putting the code directly (Can the moderators tell me the cause)