I am looking for some code to cocatenate and delete. Below is an example.

Raw Data:

A B C
1 243A030 SQYD Geosynthetic Reinforcement
2 Type 1
3 243B001 SQYD Expanded Mesh

Formatted Data:

A B C
1 243A030 SQYD Geosynthetic Reinforcement Type 1
2 243B001 SQYD Expanded Mesh

I was using a function to Concatenate, but I want to combine it in a macro
to delete the row afterwards (See Row 2 in Raw Data). The "if" statement I
was using -

=if(A2="",CONCATENATE(C1," ",C2),C1)

If it Concatenates, I want it to delete. I am working with about 9600 rows
of data. Any help would be greatly appreciated. Thanks so much.

Try the below macro which works on the activesheet. Col A,B,C

Sub Macro()
Dim lngRow As Long, strData As String
For lngRow = Cells(Rows.Count, "c").End(xlUp).Row To 1 Step -1
If Trim(Range("A" & lngRow)) = "" Then
strData = Trim(Range("c" & lngRow))
Rows(lngRow).Delete
lngRow = lngRow - 1
Range("C" & lngRow) = Trim(Range("C" & lngRow) & " " & strData)
strData = ""
End If
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"cranen" wrote:

I am looking for some code to cocatenate and delete. Below is an example.

Raw Data:

A B C
1 243A030 SQYD Geosynthetic Reinforcement
2 Type 1
3 243B001 SQYD Expanded Mesh

Formatted Data:

A B C
1 243A030 SQYD Geosynthetic Reinforcement Type 1
2 243B001 SQYD Expanded Mesh

I was using a function to Concatenate, but I want to combine it in a macro
to delete the row afterwards (See Row 2 in Raw Data). The "if" statement I
was using -

=if(A2="",CONCATENATE(C1," ",C2),C1)

If it Concatenates, I want it to delete. I am working with about 9600 rows
of data. Any help would be greatly appreciated. Thanks so much.

Awesome. I really, really appreciate your help.

"Jacob Skaria" wrote:

Try the below macro which works on the activesheet. Col A,B,C

Sub Macro()
Dim lngRow As Long, strData As String
For lngRow = Cells(Rows.Count, "c").End(xlUp).Row To 1 Step -1
If Trim(Range("A" & lngRow)) = "" Then
strData = Trim(Range("c" & lngRow))
Rows(lngRow).Delete
lngRow = lngRow - 1
Range("C" & lngRow) = Trim(Range("C" & lngRow) & " " & strData)
strData = ""
End If
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"cranen" wrote:

I am looking for some code to cocatenate and delete. Below is an example.

Raw Data:

A B C
1 243A030 SQYD Geosynthetic Reinforcement
2 Type 1
3 243B001 SQYD Expanded Mesh

Formatted Data:

A B C
1 243A030 SQYD Geosynthetic Reinforcement Type 1
2 243B001 SQYD Expanded Mesh

I was using a function to Concatenate, but I want to combine it in a macro
to delete the row afterwards (See Row 2 in Raw Data). The "if" statement I
was using -

=if(A2="",CONCATENATE(C1," ",C2),C1)

If it Concatenates, I want it to delete. I am working with about 9600 rows
of data. Any help would be greatly appreciated. Thanks so much.