Concatenation Struggle

[Darrin]

I have a 29000 row dataset. Unfortunately, some of the data imported 'text
wrapped' into the cell a row down, the rest of the row has blank cells. I am
trying to modify a macro to take the text from the cell below and concatenate
the text in that cell to the cell immediately above, and then delete the
empty row.
Looks like below.

945 S Tube Dr. 9 Ft E and 10 Ft S of Drive
in Cir
1000 E Ninemile Rd 15 W of mailbox

I need to get the 'in Cir' text from the cell its in to the end of the text
above it and then delete the empty row.

What do I need to add to the code below to complete what I am attempting to
do.

Sub DeleteBlankRows1()
'Deletes the entire row within the selection if the ENTIRE row contains no
data.

'We use Long in case they have over 32,767 rows selected.
Dim b As Integer
Dim i As Long

'We turn off calculation and screenupdating to speed up the macro.
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
b = 0
'We work backwards because we are deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If

Next i

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Thanks in advance for suggestions and help.

 

[Bob Phillips]

is this in 2 columns, with the wrapped data in column B? If not how do we
know which rows to concatenate up to the previous row?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bernie Deitrick]

Darrin,

If your column with the wrapped text is always the same, you could use the macro below. For
example, if the column with the wrapped text is column C, and a blank in column B means that the row
should be removed, use

myConcatCol = 3
myBlankCol = 2

HTH,
Bernie
MS Excel MVP

Sub Combine()
Dim myRows As Long
Dim myConcatCol As Integer
Dim myBlankCol As Integer

myConcatCol = 2
myBlankCol = 1

Range("A:B").EntireColumn.Insert
myRows = Range("C65536").End(xlUp).Row
Range("A1:A" & myRows).Formula = _
"=IF(RC[" & myConcatCol & "]="""",""Delete Me"",ROW())"
Range("B1:B" & myRows).FormulaR1C1 = _
"=IF(R[1]C[" & myBlankCol & "]="""",RC[" _
& myConcatCol & "]& "" "" & R[1]C[" & _
myConcatCol & "],RC[" & myConcatCol & "])"
With Range("A:B")
.Copy
.PasteSpecial Paste:=xlValues
End With
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending
Columns("A:A").Find(What:="Delete Me", After:=Range("A1")).Select
Range(Selection, Selection.End(xlDown)).EntireRow.Delete
Range("A1").EntireColumn.Delete
Range("A:A").Copy Columns(myConcatCol + 1)
Range("A1").EntireColumn.Delete
End Sub

 

[Darrin]

Thanks Bernie, the code you gave me worked perfectly. Sorry Bob for giving a
more detailed explanation of the data I'm working with.

Darrin,

If your column with the wrapped text is always the same, you could use the macro below. For
example, if the column with the wrapped text is column C, and a blank in column B means that the row
should be removed, use

myConcatCol = 3
myBlankCol = 2

HTH,
Bernie
MS Excel MVP

Sub Combine()
Dim myRows As Long
Dim myConcatCol As Integer
Dim myBlankCol As Integer

myConcatCol = 2
myBlankCol = 1

Range("A:B").EntireColumn.Insert
myRows = Range("C65536").End(xlUp).Row
Range("A1:A" & myRows).Formula = _
"=IF(RC[" & myConcatCol & "]="""",""Delete Me"",ROW())"
Range("B1:B" & myRows).FormulaR1C1 = _
"=IF(R[1]C[" & myBlankCol & "]="""",RC[" _
& myConcatCol & "]& "" "" & R[1]C[" & _
myConcatCol & "],RC[" & myConcatCol & "])"
With Range("A:B")
.Copy
.PasteSpecial Paste:=xlValues
End With
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending
Columns("A:A").Find(What:="Delete Me", After:=Range("A1")).Select
Range(Selection, Selection.End(xlDown)).EntireRow.Delete
Range("A1").EntireColumn.Delete
Range("A:A").Copy Columns(myConcatCol + 1)
Range("A1").EntireColumn.Delete
End Sub

I have a 29000 row dataset. Unfortunately, some of the data imported 'text
wrapped' into the cell a row down, the rest of the row has blank cells. I am
trying to modify a macro to take the text from the cell below and concatenate
the text in that cell to the cell immediately above, and then delete the
empty row.
Looks like below.

945 S Tube Dr. 9 Ft E and 10 Ft S of Drive
in Cir
1000 E Ninemile Rd 15 W of mailbox

I need to get the 'in Cir' text from the cell its in to the end of the text
above it and then delete the empty row.

What do I need to add to the code below to complete what I am attempting to
do.

Sub DeleteBlankRows1()
'Deletes the entire row within the selection if the ENTIRE row contains no
data.

'We use Long in case they have over 32,767 rows selected.
Dim b As Integer
Dim i As Long

'We turn off calculation and screenupdating to speed up the macro.
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
b = 0
'We work backwards because we are deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If

Next i

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Thanks in advance for suggestions and help.

 

[Bob Phillips]

No problem, the result is all.

Bob

Thanks Bernie, the code you gave me worked perfectly. Sorry Bob for giving a
more detailed explanation of the data I'm working with.

Darrin,

If your column with the wrapped text is always the same, you could use the macro below. For
example, if the column with the wrapped text is column C, and a blank in column B means that the row
should be removed, use

myConcatCol = 3
myBlankCol = 2

HTH,
Bernie
MS Excel MVP

Sub Combine()
Dim myRows As Long
Dim myConcatCol As Integer
Dim myBlankCol As Integer

myConcatCol = 2
myBlankCol = 1

Range("A:B").EntireColumn.Insert
myRows = Range("C65536").End(xlUp).Row
Range("A1:A" & myRows).Formula = _
"=IF(RC[" & myConcatCol & "]="""",""Delete Me"",ROW())"
Range("B1:B" & myRows).FormulaR1C1 = _
"=IF(R[1]C[" & myBlankCol & "]="""",RC[" _
& myConcatCol & "]& "" "" & R[1]C[" & _
myConcatCol & "],RC[" & myConcatCol & "])"
With Range("A:B")
.Copy
.PasteSpecial Paste:=xlValues
End With
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending
Columns("A:A").Find(What:="Delete Me", After:=Range("A1")).Select
Range(Selection, Selection.End(xlDown)).EntireRow.Delete
Range("A1").EntireColumn.Delete
Range("A:A").Copy Columns(myConcatCol + 1)
Range("A1").EntireColumn.Delete
End Sub

I have a 29000 row dataset. Unfortunately, some of the data imported 'text
wrapped' into the cell a row down, the rest of the row has blank cells. I am
trying to modify a macro to take the text from the cell below and concatenate
the text in that cell to the cell immediately above, and then delete the
empty row.
Looks like below.

945 S Tube Dr. 9 Ft E and 10 Ft S of Drive
in Cir
1000 E Ninemile Rd 15 W of mailbox

I need to get the 'in Cir' text from the cell its in to the end of the text
above it and then delete the empty row.

What do I need to add to the code below to complete what I am attempting to
do.

Sub DeleteBlankRows1()
'Deletes the entire row within the selection if the ENTIRE row contains no
data.

'We use Long in case they have over 32,767 rows selected.
Dim b As Integer
Dim i As Long

'We turn off calculation and screenupdating to speed up the macro.
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
b = 0
'We work backwards because we are deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If

Next i

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Thanks in advance for suggestions and help.