View Full Version : Cut, Paste, and Fill Down Stock Name Next to Ticker
zljordan
12-12-2012, 07:52 AM
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
12-12-2012, 02:48 PM
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
12-13-2012, 05:27 AM
CodeNinja, any special reason you used:
Sheet1.Cells(Sheet1.Range("B" & l).End(xlUp).Row, 2)
over:
Sheet1.Range("B" & l).End(xlUp)
?
zljordan
12-13-2012, 07:49 AM
Thank you so much. That worked perfectly.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.