trying to check if a cell value exists (duplicated) in a non contiguous row, where some columns might be hidden

Question

am working on sheet and using the vba for the first time and i love it. but been stuck in one thing for the last few days, after all the reading and searching can not figure how to do this part, here is the scenario I have:

locked sheet and workbook, user can only edit/entre values (numbers) in cells C8:G8 and I8:X8, column H always blank and host no value.

the user is able to hide columns in C8:G8 and I8:X8 if he need to use certain number of columns.

trying to set a macro to identify if a value has been entered more then once within the entire range C8:X8 (excluding H it is empty and any other columns if hidden)

I started with countif and give the perfect results only if all columns are visible:

Sub dup()

 Application.EnableEvents = False

  Dim x As Variant 'tried with range
  Dim n As Variant 'tried with range
  Dim rng1 As Range 'tried with variant

  Set rng1 = Range("C8:X8")

  For Each x In rng1.SpecialCells(xlCellTypeVisible)

        If Application.WorksheetFunction.CountIf(rng1, x) > 1 Then

           x.Offset(4) = "3" 'used for conditional formatting

       Else

           x.Offset(4) = "10" 'used for conditional formatting
      End If

   Next

   Application.EnableEvents = True

   End Sub

still work when some columns are hidden but it does check through hidden columns and this is not what i want (i want it to skip hidden columns) some search and reading find out the countif is unable to get the cell property if visible or hidden. tried both options application.countif and application.worksheetfunction.countif

so tried application.match but no luck

    For Each x In rng1

If Not IsEmpty(x) Then

    n = Application.match(x.Value, rng1.Value, 0)
    If Not IsError(n) Then

    x.Offset(4) = "3"

    Else

    x.Offset(4) = "10"
    End If
End If

Next

tried application.hlookup and not able to get the desired result :

For Each x In rng1

If Not IsEmpty(x) Then

    n = Application.HLookup(x.Value, rng1.Value, 1, False)
    If Not IsError(n) Then

    x.Offset(4) = "3"

    Else

    x.Offset(4) = "10"
    End If
End If

Next

it will match the cell itself and look only in the first part of the range C8:G8.

just to explain about the hidden columns situation, the user can hide/show 1,2,3,4 and 5 columns in the first range (if user select 2, only columns C8:D8 will be visible) same apply for range I8:X8, if user select 5 only I8:M8 will be visible) so there will be a case where a hidden column will be in between visible columns.

find few answers on how to use SumProduct(subtotal,...) as a formula only and could not covert it to a VBA.

any recommendation and advise will be appreciated.

Answer 1

Please try this solution.

Sub Dup()

    Const Sep As String = "|"       ' select a character that
                                    ' doesn't occur in Rng

    Dim Rng As Range
    Dim Arr As Variant
    Dim SearchString As String
    Dim n As Integer
    Dim i As Integer

    ' needed only if you have event procedures in your project:-
    Application.EnableEvents = False

    Set Rng = Range("C8:X8")
    Arr = Rng.Value
    SearchString = Sep
    For i = 1 To UBound(Arr, 2)
        If Not Columns(Rng.Cells(i).Column).Hidden Then
            SearchString = SearchString & Arr(1, i) & Sep
        End If
    Next i

    For i = 1 To UBound(Arr, 2)
        ' skip blanks, incl. column H, & hidden cells
        If (Not Columns(Rng.Cells(i).Column).Hidden) And (Len(Arr(1, i)) > 0) Then
            n = InStr(SearchString, Sep & Arr(1, i) & Sep)
            n = InStr(n + 1, SearchString, Sep & Arr(1, i) & Sep)
            With Rng.Cells(i)
                If .Column <> 8 Then    ' skip column H
                    .Offset(4).Value = IIf(n > 0, 3, 10)
                    ' Note that "3" is a string (text) whereas 3 is a number
                    ' It's unusual to enter a number as text because it's use
                    ' for calculations is greatly impaired.
                    ' However, you may modify the above line to write strings
                    ' instead of numbers.
                End If
            End With
        End If
    Next i

    Application.EnableEvents = True
End Sub

The sub assigns all non-hidden values in the Range to to an array and then reads them into a string (SearchString) in which they are separated by a special character which can be re-defined. All values exist in this string at least once. The second loop looks for the existing value which must be both followed and preceded by the special character because "a" would be found in "ab", "a|" in "ba|" but "|a|" is unambiguous. Then a second search (Instr), starting from after where the first match was found, determines if a duplicate exists. The Iif function then sets the value in the cell 4 rows below the examined cell. Note that the array index is identical to the cell number in the range because of the way the array was created.

Since the Instr function will "find" a null string in position 1 and consider it a duplication by default, null strings aren't processed, not setting any number for the purpose of CF. Column H should therefore be omitted. However, if column H should have any value the CF number will still not be written.

As the sub is called by an event procedure the Application's EnableEvents property should be set in that procedure, not in the sub. This is for greater clarity of the code and has no bearing on the functionality unless the vent procedure also calls other procs.

Answer 2

@Variatus, Sorry to get back on this, after further tests i think i found an issue, if i try to hide any clomun from range C8:G8 (ex : G8 and let say it has same value as M8) the Arr will only look through C8:F8 only, for some reason it doesn't go all the way to X8, and it will mark M8 as duplicate.

or even if the duplicate value is withing I8:X8 it wont find it because the Arr stop at the first hidden cell from the first range

any advise will be appreciated