Combining Cells on Loop until Last Character equal >

[keeblerkev]

Hello,

I have had great success with people's help on this site and hoping the success continues with this problem. I have two parts to my spreadsheet that I am struggling with in regardsin to combining cells.

Part 1:
My spreadsheet comes looking like this below:
| A | B | C | D | E |...
|<FONT>How | Who | What | Why | ?<BR></FONT></P> |

I need this to be combined all in one cell to read: <FONT>How, What, Why, ?<BR></FONT></P> (Including commas between values that were in separate cells)

The catch is that in this example it goes to Column E, but it various from each spreadsheet on where the question ends. The constants is that the question always starts at Coulmn D and the last cell always ends with </P>.

Is there a way to run a loop to combine these cells until the last character is ">"?

PART 2:
Following the combining in part 1, I need a loop that will combine cells until it reaches a blank cell. Once part 1 works, the constant would be that this data would start in Column E and then continue until a blank cell.

Please let me know if i my dreams of making this spreadsheet user friendly is just out of reach.

Thank you,
Kev

 

[CharlesChuckieCharles]

Works for me 614 rows or 1067 columns, no problem

 

[MickG]

Try this:-
NB:_ Results to "A1":- Alter to suit !!!!

[COLOR="Navy"]Sub[/COLOR] MG04Apr02
[COLOR="Navy"]Dim[/COLOR] Temp        [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] RngAc       [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ac          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] RngAc = Range("D1", Cells(1, Columns.Count).End(xlToLeft))
         [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Ac [COLOR="Navy"]In[/COLOR] RngAc
         Temp = Temp & ", " & Ac
         [COLOR="Navy"]If[/COLOR] InStr(Ac, ">") > 0 [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] For
             Ac = vbNullString
              [COLOR="Navy"]Next[/COLOR] Ac
               Range("A1") = Mid(Temp, 2)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[keeblerkev]

Good morning CharlesChuckCharles,

You are right, it does work. It was user error with my inputs that were the problem. Unfortunately, I plan to provide this macro to multiple users and would like to reduce the amount of input they need to enter (and reduce error) for these reports.

Any chance being able to make this part of the VBA macro without inputs?

 

[keeblerkev]

MickG,

This is great! This is an Awesome code. It works great except for the fact that my first cell sometimes starts with "<>" which then stops it. Is there a way it can recognize only the last value in the cell and stop only when that value is = ">"?

Also I need to be able to delete only the cells it combined. so is there a way to delete these cells, and shift everything to the left?

I feel like we are so close to actually making this work. I tried the code below but it did won't for some reason. Any ideas?

Sub MG04Apr02()
Dim Temp        As String
Dim RngAc       As Range
Dim Ac          As Range
Set RngAc = Range("D1", Cells(1, Columns.Count).End(xlToLeft))
         For Each Ac In RngAc
         Temp = Temp & ", " & Ac
          If Right$(Cells(1, Columns.Count).Value2, 1) = ">" Then Exit For
             Ac = vbNullString
              Next Ac
               Range("D1") = Mid(Temp, 2)
End Sub

 

[MickG]

If the last cell you want to include has the sign >
With no other characters then you can just change that line to:-

If  ac = ">" Then Exit For

 

[CharlesChuckieCharles]

Use this inconjunction with the previous function

Public Sub JoinCells()
Dim MySep As String
'MySep = Chr(44) & Chr(34) & Chr(32) & Chr(44)

ActiveSheet.Range("D2").Value = jointhecells(Range("E2"), 0, True, ", ", ">")
End Sub

Joincells is the macro you call

 

[keeblerkev]

Unfortunately, the last cell usually has some like "etc.) < br> < /Font> < /P>"

But it various depending on where the data is coming from, but the last cells I want to combine always ends with something and ">"

 

[CharlesChuckieCharles]

You'll need to replace

For Each myCell In myRange
If Not (bDontStop) And myCell.Value = strStopString Then


WITH

For Each myCell In myRange
If Not (bDontStop) And InStr(1, myCell.Value, strStopString) > 0 Then

 

[keeblerkev]

For some reason it just copies the data in "E2" over and over again in "D2." It is moving right.

 

[MickG]

Perhaps:-

[COLOR="Navy"]Sub[/COLOR] MG04Apr14
[COLOR="Navy"]Dim[/COLOR] Temp        [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] RngAc       [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ac          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] RngAc = Range("D1", Cells(1, Columns.Count).End(xlToLeft))
         [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Ac [COLOR="Navy"]In[/COLOR] RngAc
         Temp = Temp & ", " & Ac
         [COLOR="Navy"]If[/COLOR] Right(Trim(Ac), 1) = ">" [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] For
             Ac = vbNullString
              [COLOR="Navy"]Next[/COLOR] Ac
               Range("A1") = Mid(Temp, 2)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick