Concatenation Struggle

G

Guest

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.
 
B

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)
 
B

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
 
G

Guest

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



Bernie Deitrick said:
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 said:
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.
Click to expand...
Click to expand...
 
B

Bob Phillips

No problem, the result is all.

Bob

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



Bernie Deitrick said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Define Range in Deleting Empty Rows 1
delete row if no data, non-contiguous range problem 12
Run Macro on All Worksheets in a workbook 7
Problem with deleting blank rows or last row 4
deleting blank rows for up to 60000 rows of data 3
Newb: Asking about a tweak in code 2
Paste failing after cut 7
delete rows if cell in row contains "a" or "o" or empty 2

Top