Ignore empty cells when doing horizontal sort with VBA

I am trying to do a horizontal sort for 6 columns of data per row. I then want to concat all 6 columns into one single cell. The VBA code I am using works fine except for a situation in which there is an empty cell between two columns of data.

As per the example below, code works fine for first two records. I get a run-time error at the third record because the cell in second column is blank. (sample attached with expected results, if needed). Thanks

Column 1.........Column 2..............Column3..........Expected Result
2222...............5555.............................................22225555
5555...............2222.............................................22225555
5555............................................2222................22225555

Please Login or Register  to view this content.

For example: