CODEWITHSUNDEEP
vbaexcel
sparkyrules77
sparkyrules77

Reputation: 31

VBA delete rows

I am trying to write a VBA code for excel that deletes rows based on the contents of certain cells (all in the same column). My program right now looks like this:

Sub removeCol()
Dim i As Integer
i = 1
Do While i < 10
If (Cells(i, 2).Text = "a") Then
Rows(i).Delete
i = i + 1
Else
i = i + 1
End If
Loop
End Sub

I would be very grateful for any tips! Right now this thing is not having any affect at all.

Upvotes: 1

Views: 478

Answers (2)

Vasily
Vasily

Reputation: 5782

one of the variants is:

Sub test()
    Dim i&, x&
    i = [B:B].Cells.Find("*", , , xlByRows, , xlPrevious).Row 'get last row in "B" column
    For x = i To 1 Step -1
        If LCase(Cells(x, 2).Value2) = "a" Then Rows(x).Delete 'Lcase to remove case sensivity, due to "a" not equal to "A"
    Next x
End Sub

if you want check cells contents using contains method, then you can use this:

Sub test2()
    Dim i&, x&
    i = [B:B].Cells.Find("*", , , xlByRows, , xlPrevious).Row
    For x = i To 1 Step -1
        If LCase(Cells(x, 2).Value2) Like "*a*" Then Rows(x).Delete
    Next x
End Sub

or this:

Sub test3()
    Dim i&, x&
    i = [B:B].Cells.Find("*", , , xlByRows, , xlPrevious).Row
    For x = i To 1 Step -1
        If InStr(1, Cells(x, 2).Value2, "a", vbTextCompare) > 0 Then Rows(x).Delete
    Next x
End Sub

Upvotes: 2

cboden
cboden

Reputation: 823

Sub removeCol()
    Dim i As Integer

    For i = 10 To 1 Step -1
        If Cells(i, 2) = "a" Then
            Rows(i).Delete
        End If
    Next
End Sub

Upvotes: 3

Related Questions