I want to combine the fixed row value with diffrent column value but by skipping blank cells. I've a code but it is not working. Please suggest

enter image description here

Public Function SuperJoin(r1 As Range, r2 As Range, IgnoreBlanks As Boolean) As String
Dim i As Long, j As Long
Dim s As String

j = r2.Cells.Count
For i = 1 To j
  If IgnoreBlanks = True Then
    If r2.Cells(i) = True Then
        If r2.Cells(i).Value <> "" Then s = s & "-" & r2.Cells(i)
    End If
    If r2.Cells(i) = True Then
        s = s & "-" & r2.Cells(i)
    End If
  End If

i = r1.Count

For j = 1 To i
    SuperJoin = SuperJoin & "----" & r1(1, j) & r2(1, j)
Next j

SuperJoin = Mid(SuperJoin, 5)

End Function
New contributor
neelabh is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

1 Answer 1


If you want to do it without VBA this ugly formula will do the job.

=IF(ISBLANK(B5),"",B$1&"-"&B5&", ") &IF(ISBLANK(C5),"",C$1&"-"&C5) &IF(ISBLANK(D5),"", (IF(ISBLANK(C5),D$1&"-"&D5,", "&D$1&"-"&D5)))

It is in cell B12 in the screen cap below. Unfortunately, the formula always assumes there are at least two columns with data or it will add a unnecessary , as the results in E show. (Cell B13)

enter image description here

Your Answer

neelabh is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.