View Full Version : VBA code to delete certain rows.

bopha99

03-27-2013, 02:51 PM

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.

mdmackillop

03-27-2013, 04:29 PM

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

bopha99

03-27-2013, 06:10 PM

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

Amazing! Thanks!

bopha99

03-27-2013, 06:16 PM

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

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.

bopha99

03-27-2013, 06:22 PM

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.

mdmackillop

03-28-2013, 10:25 AM

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

bopha99

03-28-2013, 12:24 PM

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

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!

mdmackillop

03-28-2013, 03:41 PM

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

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.