if B2 blank, append B1 to A1

[mameha1977]

My starting data:

15-25mm | bob
1/2-1 |
15-25mm | john
1/2-1 | tony
15-25mm | craig
1/2-1 |

If a cell in B col is blank, I wish to:
1) append the contents of B1 to A1 inside brackets
2) delete the row that had a blank value in col B

Data above should end up like this:

15-25mm (1/2-1) | bob
15-25mm | john
1/2-1 | tony
15-25mm (1/2-1) | craig

Can it be done?

 

[Michael M]

This will answer the 2nd part of the question.
Sub Deline()
Dim r As Long, lrow As Long
Application.ScreenUpdating = False
lrow = Worksheets("sheetname").Cells(Rows.Count, "A").End(xlUp).Row
For r = lrow To 1 Step -1
If Range("B" & r).Value = "" = True Then
Rows(r).Delete
End If
Next r
End Sub

For the first part have a look at the CONCATENATE function
I think you will have to insert a new column between Col A and B and then you will have to use something like
=CONCATENATE(A1&" "&"("&A2&")")

Regards
Michael M

 

[mameha1977]

Thanks, how do I use that first piece of code? is it VB or something?

 

[Michael M]

Hi
Yes, it's VBA. There really isn't any other way to delete lines automatically apart from manually.
Press Alt F11 and select "This workbook" in the left hand window.
Then paste the code in the right hand pane.

Regards
Michael M

 

[Peter_SSs]

This is my suggestion. To implement (after making a backup of your file) ...

1. Right click the sheet tab name and choose "View Code"
2. In the Visual Basic window that opens, use the menus Insert|Module.
3. Copy the code below from the board and paste in the main right hand window that opened after step 2.
4. Close the VB window.
5. From your sheet menus Tools|Macro|Macros... select the "Rearrange" macro and click Run. (It can be assigned to a Toolbar button or to a shortcut key if you wish to use it regularly)

From your description, I have assumed that there will never be two (or more) consecutive rows in column B that are blank. If that assumption is incorrect, my code will most likely not do what you want... but then again you haven't said what you would want in this circumstance... which brings me back to the reason I made the assumption in the first place.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Rearrange()<br>    <SPAN style="color:#00007F">Dim</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> myRange = Range("B2", Range("A" & Rows.Count).End(xlUp).Offset(, 1))<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> myRange<br>        <SPAN style="color:#00007F">If</SPAN> c.Value = "" <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> c.Offset(-1, -1)<br>                .Value = .Value & " (" & .Offset(1).Value & ")"<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            c.EntireRow.Delete<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    Columns("A").AutoFit<br>    <SPAN style="color:#00007F">Set</SPAN> myRange = <SPAN style="color:#00007F">Nothing</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>