Need Help moving text from one cell location to another

[rsajdak]

I was hoping someone here could help me with a macro.

I have a spreadsheet that has all of Column A blank
Column B contains about 1000 rows of text, which has the following layout: spec name “SI_xxxxx”, then below it text describing the spec. This repeats over and over going down the rows

What is want to do is to 1) verify that the cell is a spec by confirming that it begins with “SI_”, 2) move the spec name left into column A and then down one row so that it is immediately left of the its description in Column B, 3) repeat this process all the way down the row until the end.

If anyone could help, I’d greatly appreciate it.

 

[just_jon]

Welcome to MrExcel -

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> foo()
<SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row).SpecialCells(xlCellTypeConstants)
    <SPAN style="color:#00007F">If</SPAN> c.Value <SPAN style="color:#00007F">Like</SPAN> "SI_*" <SPAN style="color:#00007F">Then</SPAN>
        c.Offset(1, -1).Value = c.Value
        c.Value = vbNullString
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> c
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[rsajdak]

Thank you very much that worked awesome!!

I forget to specify one thing though: Can the macro be customized to that it only affects a shaded range that I define with the mouse?

Thank you in advance.

 

[just_jon]

Thank you very much that worked awesome!!

I forget to specify one thing though: Can the macro be customized to that it only affects a shaded range that I define with the mouse?

Thank you in advance.

Sure --

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> foo()
<SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Selection.SpecialCells(xlCellTypeConstants)
    <SPAN style="color:#00007F">If</SPAN> c.Value <SPAN style="color:#00007F">Like</SPAN> "SI_*" <SPAN style="color:#00007F">Then</SPAN>
        c.Offset(1, -1).Value = c.Value
        c.Value = vbNullString
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> c
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>


</FONT>

 

[rsajdak]

THANK YOU THANK YOU. I worked great! I have 4000 lines that I though I had to sort by hand. You saved me!