hi, !

__ 1 __
I made a revision as well. Not every cell in those 5 columns I am joining together contain data -
MyCell.Formula = RTrim(Join(.Transpose(.Transpose(MyCell.Resize(, 5))), " "))
It shaves off the extra spaces that get tagged on to the right.

1) and, what if you have more blank cells ?... RTrim won't remove middle spaces
try using the application trim (worksheet function) i.e.
MyCell.Formula = .Trim(Join(.Transpose(.Transpose(MyCell.Resize(, 5))), " "))

__ 2 __
How does this work? Again, a solution that does just the trick and is efficient
but I don't understand the nested transpose function. I've never used Join (until now I didn't know it existed).
It's really that one line of code that I'd like clarification on...

2) the first .Transpose builds a transposed array: rows2columns or columns2rows
second one restores rows from rows / columns from columns
after this, the array can be "joined" using the specified character

join is a vba function available since vba6 (xl 2000)

hth,
hector.

__ OP __
i'm not so sure to guess your layout exactly (even to have requested all the information "pending")
(but) i would try to avoid (when possible) several nested for...next, so...

give a try to the following alternate and...

With ActiveSheet
.Range("a1").AutoFilter Range(PrefBAdd).Column, "<y"
With .AutoFilter.Range
For Each myCell In Range(HAdd).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
With Application
myCell.Value = Join(.Transpose(.Transpose(myCell.Resize(, 5))), " ")
End With
Next
End With
End With