I have a large spreadsheet that has 2 rows for each ticker symbol. In these rows, the first row gives the number of shares outstanding in the beginning and the next row gives the ending number of shares outstanding. My problem is that there are some tickers with only 1 row. I want to delete these rows. I'm not much of a VBA person. Is there a VBA code that can be written to delete the single rows? The file is too lare to upload on here, but I can email if needed. Thanks.
[VBA]Sub DelSingle()
Dim i As Long, r As Long
r = Cells(Rows.Count, 1).End(xlUp).Row
For i = r To 1 Step -1
If Application.CountIf(Columns(1), Cells(i, 1)) = 1 Then Cells(i, 1).EntireRow.Delete
Next
End Sub
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Originally Posted by mdmackillop
Amazing! Thanks!
I am noticing that there are sometimes 3 rows instead of only 2 rows. Is there an addition to the code above so that there can only be 2 rows? I would like the 1st row highest to the top to be deleted if there are 3 rows. Thanks.
actually, if it is possible, instead of the row highest to the top, if the middle row could be erased when there are instances of 3 that would be great. Thanks.
[VBA]Sub DelSingle()
Dim i As Long, r As Long
r = Cells(Rows.Count, 1).End(xlUp).Row
For i = r To 1 Step -1
Cells(i, 1).Select
If Application.CountIf(Columns(1), Cells(i, 1)) = 1 Then Cells(i, 1).EntireRow.Delete
If Application.CountIf(Range(Cells(1, 1), Cells(i, 1)), Cells(i, 1)) = 2 And _
Application.CountIf(Columns(1), Cells(i, 1)) = 3 Then
Cells(i, 1).EntireRow.Delete
End If
Next
End Sub[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
This is great! One last modification to the 1st code. What would be the first code of instead deleting the 2nd row in instances of 3 rows, but deleting all 3 rows when there are 3 rows. So the code would be for deleting all instances of 1 row and all instances of 3 rows. Thanks!
[VBA]Sub DelSingleThree()
Dim i As Long, r As Long
r = Cells(Rows.Count, 1).End(xlUp).Row
For i = r To 1 Step -1
If Application.CountIf(Columns(1), Cells(i, 1)) = 1 Then Cells(i, 1).EntireRow.Delete
If Application.CountIf(Columns(1), Cells(i, 1)) = 3 Then Cells(i, 1).Offset(-2).Resize(3).EntireRow.Delete
Next
End Sub[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
Reply With Quote