Cut, Paste, and Fill Down Stock Name Next to Ticker

[zljordan]

Please see attachment.

I am trying to create a macro that will cut the name of the specific stock that is listed only once above the ticker symbol, and paste the name next to each ticker symbol. See Rows 4-6 (i.e. Bristol-Myers next to bmy).

Also I then need to delete all rows where any cells in column "A" are blank.

Thank you.

[CodeNinja]

zlJordan,
The attached macro does what you ask... I made the assumption that a ticker symbol never can be more than 6 characters (I think 5 is the max, but I'm not sure) and I assume you always have at least 1 empty row between stocks... It seems your worksheet holds to those assumptions...

Good luck...

Sub test()
Dim l As Long

For l = Sheet1.Range("B65536").End(xlUp).Row To 4 Step -1
If Sheet1.Cells(l, 2) = "" Then
'blank row, delete...
Sheet1.Cells(l, 1).EntireRow.Delete
Else
If Len(Sheet1.Cells(l, 2)) > 6 Then 'I don't think ticker symbols can be > 6 char
Sheet1.Cells(l, 1).EntireRow.Delete
Else
'not a stock name, must be a stock
Sheet1.Cells(l, 1) = Sheet1.Cells(Sheet1.Range("B" & l).End(xlUp).Row, 2)
End If
End If
Next l
End Sub

[p45cal]

CodeNinja, any special reason you used:
Sheet1.Cells(Sheet1.Range("B" & l).End(xlUp).Row, 2)
over:
Sheet1.Range("B" & l).End(xlUp)
?

[zljordan]

Thank you so much. That worked perfectly.