UtterAccess Discussion Forums > Vba To Filter And Delete Rows

Full Version: Vba To Filter And Delete Rows

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

lyonder2000

May 14 2012, 08:41 AM

I have a spreadsheet that I receive weekly and perform the same "data cleaning" tasks. Being a beginner Excel VBA user, I am impressed with what I've been able to accomplish so far but I have reached a roadblock. I am trying to delete certain rows from the file. The file contains data in a page-like format so when the home system exports it to me, I have a footer at the bottom (or every 45 rows or so). I would like to filter based off the wildcard text "Loss" which is in each row, and delete these rows.

I tried this but it did not work

Selection.AutoFilter field:=1, Criteria1:="*Loss*", Operator:=xlAnd
Columns("A1:A26000").Select
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete

Any help would be greatly appreciated.

Bob G

May 14 2012, 09:31 AM

If your goal is to delete all the rows that contain the word loss then this might work for you.

Make a backup copy of the spreadsheet just in case.

CODE

Sub Macro1()

Range("a1").Select
Do

Set x = Cells.Find(What:="*loss*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

    If x Is Nothing Then Exit Sub

Cells.Find(What:="*loss*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

    Selection.FindNext(After:=ActiveCell).Activate
    Selection.Delete Shift:=xlUp
  Loop
End Sub

lyonder2000

May 14 2012, 09:45 AM

Thanks! Is there a way to limit it to only delete "*loss*" when found in Column A. The rows I need to delete would only be based off *Loss* being in any cell in column A.

Thanks again.

Bob G

May 14 2012, 09:57 AM

Try this one then...

CODE

Sub Macro1()

Range("a1").Select
Do

Set x = Range("A:A").Find(What:="*loss*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

    If x Is Nothing Then Exit Sub

Range("A:A").Find(What:="*loss*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

    Selection.FindNext(After:=ActiveCell).Activate
    Selection.Delete Shift:=xlUp
  Loop
End Sub

PaulBrand

May 14 2012, 10:30 AM

Or this - assuming column A will have some kind of entry

Air code - untested

CODE

ActiveCell = Range("a1")
Do Until ActiveCell = ""

If ActiveCell = "Loss" Then
ActiveCell.EntireRow.Delete

ActiveCell.Offset(,1)

Loop

carolel

May 15 2012, 06:06 AM

Or you could go back to your original idea and use autofilter then delete rows - which you can do without the need to 'select' or 'activate' any cells. Below is a piece of code I've often used in the past, modified to your criteria. Just change the sheet name as appropriate (or you could replace that entire line with "With ActiveSheet" if you really must).

The error trap is for the case when you try to run it again on the same data, or if the criteria genuinely isn't present.

Carole

CODE

Sub DeleteRows()
    Dim rg As Range

    Application.ScreenUpdating = False

    On Error GoTo ErrorHandler

    With Worksheets("Sheet1")
        Set rg = .Range("a1", .Cells(Rows.Count, "A").End(xlUp))
        rg.AutoFilter field:=1, Criteria1:="=*loss*"
        Set rg = rg.Offset(1).Resize(rg.Rows.Count - 1)
        rg.SpecialCells(xlVisible).EntireRow.Delete
        .AutoFilterMode = False
    End With
    Application.ScreenUpdating = True

ErrorHandler:
    If Err.Number = 1004 Then
        MsgBox ("Column A doesn't contain *Loss*")
        Worksheets("Sheet1").AutoFilterMode = False
        Application.ScreenUpdating = True
        Exit Sub
    End If

End Sub

lyonder2000

May 15 2012, 03:10 PM

This works, but it deletes the cell and shifts the cells up. This creates mismatches on the data by moving the fields up to the above record. At first I thought it worked. I just need to get it to delete the entire row instead of just the cell.

Is that possible?

carolel

May 16 2012, 03:43 AM

Which one of the above are you referring to?

The code I posted should delete the entire row:

CODE

rg.SpecialCells(xlVisible).EntireRow.Delete

It certainly works as expected in version 2007 (and 2003 I think). The only row it doesn't affect is Row 1, which isn't included in the filter (my data always has headers in this row).

Carole

lyonder2000

May 16 2012, 04:25 AM

Got it. Works beautifully.

Thanks all.

carolel

May 16 2012, 04:31 AM

Great!

And good luck with the Excel VBA. As you said in your original post

QUOTE

Being a beginner Excel VBA user, I am impressed with what I've been able to accomplish so far...

With practice, it just gets better!

Cheers
Carole

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.