Solved: Can This be Simplified?

[Opv]

The following script in effect concatenates the values from about eight cells:


Dim termInfo As String
termInfo = .Range(rng.Address).Offset(0, 6).Value
termInfo = termInfo & ", " & .Range(rng.Address).Offset(0, 7).Value
termInfo = termInfo & ", " & .Range(rng.Address).Offset(0, 8).Value
termInfo = termInfo & ", " & .Range(rng.Address).Offset(0, 9).Value
termInfo = termInfo & ", " & .Range(rng.Address).Offset(0, 10).Value
termInfo = termInfo & ", " & .Range(rng.Address).Offset(0, 11).Value
termInfo = termInfo & ", " & .Range(rng.Address).Offset(0, 12).Value
termInfo = termInfo & ", " & .Range(rng.Address).Offset(0, 13).Value



I am attempting to use the above script to come with a consolidated string with which to populate another cell. While this script does what it is designed to do, my problem is that some of the cells are likely going to be blank, so I am getting a lot of ", , ," in the resulting string.

I've also tried a For Next loop with i = 6 to 13, attempting to loop through the desired cells but I'm still getting the superfluous commas. I'm looking for a way to do the above but only include populated cells in the resulting string, with an "," between each populated cell.

Thanks,

Opv

[mdmackillop]

With .Range(Rng.Address)
termInfo = .Offset(, 6).Value
For i = 7 To 13
If .Offset(, i).Value <> "" Then
termInfo = termInfo & ", " & .Offset(, i).Value
End If
Next
End With

[Opv]

Thanks. That works in every instance I've tried except when .offset(0,6).value is empty, in which case it inserts a comma before the first populated cell.

[mdmackillop]

With .Range(Rng.Address)
For i = 6 To 13
If .Offset(, i).Value <> "" Then
termInfo = termInfo & ", " & .Offset(, i).Value
End If
Next
End With

[Opv]

Hmmm. That's doing the same thing, leaving a comma in front when the first cell is empty.

[mdmackillop]

I assumed that is what you wanted, as you start with a comma before the text. You can use Right or Left to trim off unwanted end values.

[Opv]

I assumed that is what you wanted, as you start with a comma before the text. You can use Right or Left to trim off unwanted end values.

Sorry I wasn't more clear. My objective is to have a comma "between" each of the populated cells. I may can work with what you have suggested. Will advise.

Thanks,

Opv

[Bob Phillips]

Dim termInfo As String
termInfo = .Range(Rng.Address).Offset(0, 6).Value & termInfo & ", " & _
.Range(Rng.Address).Offset(0, 7).Value & termInfo & ", " & _
.Range(Rng.Address).Offset(0, 8).Value & termInfo & ", " & _
.Range(Rng.Address).Offset(0, 9).Value & termInfo & ", " & _
.Range(Rng.Address).Offset(0, 10).Value & termInfo & ", " & _
.Range(Rng.Address).Offset(0, 11).Value & termInfo & ", " & _
.Range(Rng.Address).Offset(0, 12).Value & termInfo & ", " & _
.Range(Rng.Address).Offset(0, 13).Value

Do Until InStr(termInfo, ",") = 0

termInfo = Replace(termInfo, ",,", ",")
Loop

[Opv]

I got it to work using your earlier suggestion, but adding the following snippet at the point I actually populate the appropriate cell with the value of termInfo:


If Left(termInfo, 2) = ", " Then
termInfo = Mid(termInfo, 3, Len(termInfo))
End If


Thanks as always for your help.

Opv