Copy all cells in a column until next empty cell and paste into a single cell

K

Khardin

New Member
Joined
May 14, 2012
Messages
9
I believe this is a rather difficult problem. I have a very large column of data (it is actually youtube comments). The problem is that I need to sort the comments by author but comments are split into multiple cells with a non-standard number of empty cells between comments. I have been able to sort the authors, but not link the comments to author (They are in separate columns, which is how I would like it to remain). Basically what need to be done is to copy all sequential cells with text in them and stop at the first empty cell, then paste all of those cells into a single cell, ie:append strings, and then continue to the next filled cell and do the same. This would have to continue until there is a certain number, lets say 100, of cells are empty in a row to ensure all data is gathered. Your help is much appreciated, I am using Windows 7 and Excel 2010. Warning, there is some strong language in the comments, these are not my views, nor do I approve of the language used, I just wanted to provide the most accurate representation of the data that I am dealing with. Thanks again.
See examples below

What it Looks LikeExcel 2010
AB
brnm0696 2 years agoI like how the police officer is taking care business to keep the streets safe. We need more officers like him patrolling? our streets.
canadianlaxer7575 2 years ago
cabral114 2 years ago
wilmanric1 2 years agoanyone notice the fat guy cuffed to? the pole hahahahaha
kevininregina 2 years ago
elib300 2 years ago
Cypherus21 2 years agoLooked to me that although he? looked subdued, he was resisting in the fact he would not do what police would say. ie, put your hands behind your back.
baldwinslab 2 years ago
Simroz Sidhu 2 years ago
Cypherus21 2 years ago"he was resisting in the fact"
Ron Landry 2 years agoIt wasn't a lawful arrest. In a free country it's your right to resist an unlawful arrest. If a cop doesn't have the legal right to arrest you he's committing an assault, and you're allowed to defend yourself.
fax10 2 years agoBut the cop won't be charged because the Vancouver police (who investigated) felt that although he did not have the? authority to arrest, he did have reasonable grounds to believe he could arrest, and for a cop, that's enough for a defence.
AR15DAN 2 years ago
AverageNoob1 2 years ago
kuztoml65 2 years agoi read this pig will not face charges. No wonder there are james rozscos and Curtis Dagenais individuals out there that despise them. I have also had a relative wrongfully convicted due to police withholding evidence. There needs to be reform of the system or more episodes like the march 3rd day of the mayerthorpe or going to become more common. BTW i am throwing a huge March 3rd big wing of a ding dandy party which includes a pig roast. All? are invited.
AverageNoob1 2 years ago
kuztoml65 2 years ago
AverageNoob1 2 years agoyesterday was the court case (saw it on the news) and? the cops were not charges
stud1118 2 years ago
AshLionHeart 2 years ago
joeboxervr6 2 years ago
dsharvey2005 2 years ago
Joshdude151 2 years ago
TheOrigionalArroyo 2 years ago
SPmQQse 2 years ago
THCwarrior 2 years ago
john3427 2 years ago
tigerboy38 2 years agoWhat the ****!?! I can't believe that a uniform gives you the right to just kick ***, who are the goons in this video? Watch out for the police, they'll **** you up and get away? with it and there's nothing you can do about it. Defend yourself and they shoot you.

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: right"][/TD]

[TD="align: center"]25[/TD]

[TD="align: right"][/TD]

[TD="align: center"]26[/TD]

[TD="align: right"][/TD]

[TD="align: center"]27[/TD]

[TD="align: right"][/TD]

[TD="align: center"]28[/TD]

</tbody>
Sheet2

Sheet2​



What I want it to look like:
Excel 2010
AB
brnm0696 2 years agoI like how the police officer is taking care business to keep the streets safe. We need more officers like him patrolling? our streets.
canadianlaxer7575 2 years agoanyone notice the fat guy cuffed to? the pole hahahahaha
cabral114 2 years agoLooked to me that although he? looked subdued, he was resisting in the fact he would not do what police would say. ie, put your hands behind your back.
wilmanric1 2 years ago"he was resisting in the fact"It wasn't a lawful arrest. In a free country it's your right to resist an unlawful arrest. If a cop doesn't have the legal right to arrest you he's committing an assault, and you're allowed to defend yourself. But the cop won't be charged because the Vancouver police (who investigated) felt that although he did not have the? authority to arrest, he did have reasonable grounds to believe he could arrest, and for a cop, that's enough for a defence.
kevininregina 2 years agoi read this pig will not face charges. No wonder there are james rozscos and Curtis Dagenais individuals out there that despise them. I have also had a relative wrongfully convicted due to police withholding evidence. There needs to be reform of the system or more episodes like the march 3rd day of the mayerthorpe or going to become more common. BTW i am throwing a huge March 3rd big wing of a ding dandy party which includes a pig roast. All? are invited.
elib300 2 years agoyesterday was the court case (saw it on the news) and? the cops were not charges
Cypherus21 2 years agoWhat the ****!?! I can't believe that a uniform gives you the right to just kick ***, who are the goons in this video? Watch out for the police, they'll **** you up and get away? with it and there's nothing you can do about it. Defend yourself and they shoot you.

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

</tbody>
Sheet2
​
 
Last edited:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Not sure your description is what you want. If the data is as layed out in your post then just deleting the blank cells and moving the data up would suffice.

Try

Sub celldel()
Dim ColCount
Dim rwindex

ColCount = Range("C:C").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

rwindex = 1

Application.ScreenUpdating = False

Do Until rwindex = ColCount
t = "C" & rwindex

Range(t).Select
If Selection = x1Blanks Then
Selection.Delete (xlUp)
ColCount = ColCount - 1
rwindex = rwindex - 1
End If


rwindex = rwindex + 1

Loop
Application.ScreenUpdating = True


End Sub
 
Last edited:
Upvote 0
Thank you for your reply. The problem I have is that one comment (single post by 1 author) is broken up into several different cells, so if I remove the empty spaces then the comments will not line up with the correct author, there are more cells for comments than authors to go with them. I would do this manually, but there are over 4000 comments. In the example above, the 9 comment posts in the first table actually represents the 7 comments in the second table
 
Upvote 0
Khardin,

Sample raw data in Sheet2:


Excel 2007
AB
1brnm0696 2 years agoI like how the police officer is taking care business to keep the streets safe. We need more officers like him patrolling? our streets.
2canadianlaxer7575 2 years ago
3cabral114 2 years ago
4wilmanric1 2 years agoanyone notice the fat guy cuffed to? the pole hahahahaha
5kevininregina 2 years ago
6elib300 2 years ago
7Cypherus21 2 years agoLooked to me that although he? looked subdued, he was resisting in the fact he would not do what police would say. ie, put your hands behind your back.
8baldwinslab 2 years ago
9Simroz Sidhu 2 years ago
10Cypherus21 2 years ago"he was resisting in the fact"
11Ron Landry 2 years agoIt wasn't a lawful arrest. In a free country it's your right to resist an unlawful arrest. If a cop doesn't have the legal right to arrest you he's committing an assault, and you're allowed to defend yourself.
12fax10 2 years agoBut the cop won't be charged because the Vancouver police (who investigated) felt that although he did not have the? authority to arrest, he did have reasonable grounds to believe he could arrest, and for a cop, that's enough for a defence.
13AR15DAN 2 years ago
14AverageNoob1 2 years ago
15kuztoml65 2 years agoi read this pig will not face charges. No wonder there are james rozscos and Curtis Dagenais individuals out there that despise them. I have also had a relative wrongfully convicted due to police withholding evidence. There needs to be reform of the system or more episodes like the march 3rd day of the mayerthorpe or going to become more common. BTW i am throwing a huge March 3rd big wing of a ding dandy party which includes a pig roast. All? are invited.
16AverageNoob1 2 years ago
17kuztoml65 2 years ago
18AverageNoob1 2 years agoyesterday was the court case (saw it on the news) and? the cops were not charges
19stud1118 2 years ago
20AshLionHeart 2 years ago
21joeboxervr6 2 years ago
22dsharvey2005 2 years ago
23Joshdude151 2 years ago
24TheOrigionalArroyo 2 years ago
25SPmQQse 2 years ago
26THCwarrior 2 years ago
27john3427 2 years ago
28tigerboy38 2 years agoWhat the ****!?! I can't believe that a uniform gives you the right to just kick ***, who are the goons in this video? Watch out for the police, they'll **** you up and get away? with it and there's nothing you can do about it. Defend yourself and they shoot you.
Sheet2


After the macro in a new worksheet Results:


Excel 2007
AB
1AverageNoob1 2 years agoyesterday was the court case (saw it on the news) and? the cops were not charges
2brnm0696 2 years agoI like how the police officer is taking care business to keep the streets safe. We need more officers like him patrolling? our streets.
3Cypherus21 2 years agoLooked to me that although he? looked subdued, he was resisting in the fact he would not do what police would say. ie, put your hands behind your back. "he was resisting in the fact"
4fax10 2 years agoBut the cop won't be charged because the Vancouver police (who investigated) felt that although he did not have the? authority to arrest, he did have reasonable grounds to believe he could arrest, and for a cop, that's enough for a defence.
5kuztoml65 2 years agoi read this pig will not face charges. No wonder there are james rozscos and Curtis Dagenais individuals out there that despise them. I have also had a relative wrongfully convicted due to police withholding evidence. There needs to be reform of the system or more episodes like the march 3rd day of the mayerthorpe or going to become more common. BTW i am throwing a huge March 3rd big wing of a ding dandy party which includes a pig roast. All? are invited.
6Ron Landry 2 years agoIt wasn't a lawful arrest. In a free country it's your right to resist an unlawful arrest. If a cop doesn't have the legal right to arrest you he's committing an assault, and you're allowed to defend yourself.
7tigerboy38 2 years agoWhat the ****!?! I can't believe that a uniform gives you the right to just kick ***, who are the goons in this video? Watch out for the police, they'll **** you up and get away? with it and there's nothing you can do about it. Defend yourself and they shoot you.
8wilmanric1 2 years agoanyone notice the fat guy cuffed to? the pole hahahahaha
9
Results


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub MergeData()
' hiker95, 02/23/2013
' http://www.mrexcel.com/forum/excel-questions/687519-copy-all-cells-column-until-next-empty-cell-paste-into-single-cell.html
Dim r As Long, lr As Long, n As Long
Dim bc As Variant, i As Long
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=Worksheets("Sheet2")).Name = "Results"
With Worksheets("Results")
  .UsedRange.Clear
  Worksheets("Sheet2").Range("A1").CurrentRegion.Resize(, 2).Copy .Cells(1, 1)
  On Error Resume Next
  .Range("B1", .Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  On Error GoTo 0
  .Columns(1).Insert
  lr = .Cells(Rows.Count, 2).End(xlUp).Row
  With .Range("A1:A" & lr)
    .Formula = "=LEFT(B1,FIND("" "",B1,1)-1)"
    .Value = .Value
  End With
  .Range("A1:C" & lr).Sort key1:=.Range("A1"), order1:=1, key2:=.Range("B1"), order2:=1
  ReDim bc(1 To lr, 1 To 2)
  For r = 1 To lr
    n = Application.CountIf(.Columns(1), .Cells(r, 1).Value)
    If n = 1 Then
      i = i + 1
      bc(i, 1) = .Cells(r, 2)
      bc(i, 2) = .Cells(r, 3)
    Else
      i = i + 1
      bc(i, 1) = .Cells(r, 2)
      bc(i, 2) = Join(Application.Transpose(.Range("C" & r & ":C" & r + n - 1)), " ")
    End If
    r = r + n - 1
  Next r
  .UsedRange.Clear
  .Cells(1, 1).Resize(UBound(bc, 1), UBound(bc, 2)) = bc
  .Columns(1).AutoFit
  .Columns(2).WrapText = True
  .Columns(2).ColumnWidth = 100
  .UsedRange.Rows.AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the MergeData macro.
 
Upvote 0
Thank you very much hiker95, this has saved me a lot of time. I hope to one day possess the knowledge to write macros like this myself. I wish you a great night, too bad I can't offer you a nice cold beer:)
 
Upvote 0
Khardin,

You are very welcome. Glad I could help.

Thanks for the feedback, and, come back anytime.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,225,149
Messages
6,183,193
Members
453,151
Latest member
Lizamaison

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top