CODEWITHSUNDEEP
stringexcelvbarows
Dakota
Dakota

Reputation: 474

Locating specific values within cells and deleting identified rows

I've been using this site for awhile, and have been searching for a relatively simple way to achieve a task in VBA within Excel 07.

I have a column that has numerous different values, and I'm trying to locate cells in AA:AA that begin with "L-", and then from there delete the rows from the sheet. The activesheet/activebook never changes, butthe issue I'm having is that the column has blank cells every now and then. I've tried using the below code, but I'm not achieving the results I need.

Sub Remove_Expendables()
Application.ScreenUpdating = FALSE
Range("AA1").Select
Do
ActiveCell.Offset(1, 0).Select
If ActiveCell = (Left("L-",2) Then ActiveCell.ClearContents
Loop Until IsEmpty(ActiveCell.Offset(0, 2))
ActiveSheet.Columns("AA:AA").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.ScreenUpdating = TRUE
End Sub

Upvotes: 1

Views: 105

Answers (2)

brettdj
brettdj

Reputation: 55702

AutoFilter is much quicker than looping.

Sub AF_Delete()
    Application.ScreenUpdating = False
    With ActiveSheet
        .AutoFilterMode = False
        .Columns("AA").AutoFilter Field:=1, Criteria1:="=L-*"
        .AutoFilter.Range.Offset(1, 0).EntireRow.Delete        '
        .AutoFilterMode = False
    End With
    Application.ScreenUpdating = True
End Sub

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166835

Sub Remove_Expendables()
Dim c as range, rngDel as range
set c= activesheet.cells(rows.count, "AA").end(xlup)

Application.ScreenUpdating = FALSE
Do While c.row > 1
    If c.value like "L-*" Then
        if rngDel is nothing then
            set rngDel=c
        else
            set rngDel=application.union(rngDel,c)
        end if
    end if
    set c=c.offset(-1,0)
Loop 
if not rngDel is nothing then rngDel.entirerow.delete
Application.ScreenUpdating = TRUE
End Sub

Upvotes: 0

Related Questions