0

In my MS Excel spreadsheet, the Border is used to determine the end of some related data. I want to convert these related cell`s content into one cell. I want to do it by concatenate the texts inside cells of column until Border and begin with next cell and continue concatenating until next Border and so on . . . . Is there a trick for doing this? enter image description here

Thanks in advance

Community
  • 1
  • 1
Ehsan Abidi
  • 911
  • 9
  • 24

1 Answers1

2

There is a tons of thing you can do with this but I think you can get the hang of it. It is hardcoded to scan the first column, and output into the second one - you can parameterize it obviously. 

Sub concat()

' loop on first column but this could be an input
Dim max_rows As Integer
Dim start_col As Integer ' The column where your data is
start_col = 1
max_rows = ActiveSheet.UsedRange.Rows.Count ' count how many times to loop
Dim counter As Integer
counter = 1 ' this is for the output to know when we wrote out something we increment to next cell
Dim temp_string As String
temp_string = ""  ' variable to store until write out
For i = 1 To max_rows:
    Cells(i, 1).Select
    temp_string = temp_string + " " + ActiveCell.Value
    If Selection.Borders(xlEdgeBottom).LineStyle = 1 Then
        out = temp_string
        Cells(counter, 2) = out
        counter = counter + 1
        temp_string = ""
End If
Next i
End Sub
Anna Semjén
  • 787
  • 5
  • 14
  • Why one should avoid UsedRange .. See how to find last row.. https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba?noredirect=1&lq=1 – Naresh Mar 04 '20 at 11:18
  • I disagree with this - a spreadsheet that was created out of code, should never have this problem, only if you start editing it - obviously any touched cell becomes last cell even if deleted - it is not unreliable it provides expected behaviour - and as I said depending on usage there are multiple things you can do with this even hardcode 100 in case you know you'll have 100 rows – Anna Semjén Mar 04 '20 at 11:21