VBA - Count empty cols, search and replace

Question

I'm learning VBA and trying to do something a bit complicated for me. Here's the deal :

In my "H" column, I'm using the "CONCATENATE" formula to get a key of all the elements I want in my columns, for each line. As you can see, some elements aren't filled and I'm having unwanted " - " separators. I'd like to have a macro that searches and replaces the double, triple (...) separators that I don't want, and if there a line filled only with separators (i.e my H5 cell) I'd like it to be replaced with nothing instead.

The thing is, I'd like to add some columns/lines in the future and I don't want to change the macro every time I'm adding a column or a line. Consequently, it'd be great if there was a way to say to my macro: "Whenever there's a line filled with nothing but separators, replace it with nothing".

This is the part I don't know how to deal with. Could you guys give me a hint?

Thanks and sorry for the long post, here's a kawaii potato

Answer 1

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

=TEXTJOIN(" - ",TRUE,A2:G2)

UPDATE: If your version of Excel doesn't have TEXTJOIN

Function UDFTextTJoin(delimiter As String, ignore_empty As Boolean, ParamArray Text()) As String
    Dim s As String
    Dim v As Variant
    Dim x As Long

    For x = 0 To UBound(Text)
        If TypeName(Text(x)) = "Range" Then
            For Each v In Text(x)
                If Not ignore_empty Or v <> "" Then
                    If Len(s) Then s = s & delimiter
                    s = s & v
                End If
            Next
        Else

            If Not ignore_empty Or Text(x) <> "" Then
                If Len(s) Then s = s & delimiter
                s = s & Text(x)
            End If
        End If
    Next

    UDFTextTJoin = s

End Function

Answer 2

For sure there are methods to avoid the pattern from the very beginning, but this is a Macro to do a (late) cleanup:

Sub Cleanup()
    Dim cel As Range, i As Long
    With Worksheets("Products").UsedRange.Columns("J")
        For i = 1 To 5
           .Replace "- - ", "- "
        Next
        For Each cel In .Cells
            If Trim(cel.Value) = "-" Then cel.Clear
        Next
    End With
End Sub

EDIT:

Since you and I dont have TextJoin, and it is an excellent solution proposed by the pals, let us have it as UDF. You can add the code below to any non-class code module and use it as User-Defined-Formula (UDF):

Public Function TextJoin(ByVal sep As String, ByVal ignoreEmpty As Boolean, ByRef ar As Variant) As String

    Dim cel As Range
    For Each cel In ar
        If Trim(cel.Text) <> "" Or Not ignoreEmpty Then
            If TextJoin <> "" Then TextJoin = TextJoin + sep
            TextJoin = TextJoin + cel.Text
        End If
    Next
End Function