Solved

Filter and Delete

Posted on 2013-12-13
8
175 Views
Last Modified: 2013-12-16
Hi,

I need some code that will filter and delete rows bases on the last two columns

So if

FE = "CMB" & FF = "US"

THEN DELETE ENTIRE ROW

What i dont want is gaps in the data, so if that data existed in rows 94-98,i wouldnt want blank spaces there, im sure delete looks after that, but just making sure!

Thanks
Filter-and-Delete-EE.xlsx
0
Comment
Question by:Seamus2626
8 Comments
 
LVL 19

Expert Comment

by:regmigrant
ID: 39717157
Does it have to be code? you could just use autofilter and Delete row?
Reg
0
 

Author Comment

by:Seamus2626
ID: 39717166
Yes, i need this to form part of a macro which regularly does the task

Thanks
0
 
LVL 8

Expert Comment

by:5teveo
ID: 39717180
This macro deletes all string patters in Column A

Sub DeleteRows()
    Dim c As Range
    Dim SrchRng As Range
    Dim SrchStr As String
     
    Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A65536").End(xlUp))
    SrchStr = InputBox("Please Enter A Search String")
    Do
        Set c = SrchRng.Find(SrchStr, LookIn:=xlValues)
        If Not c Is Nothing Then c.EntireRow.Delete
    Loop While Not c Is Nothing
     
End Sub
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Accepted Solution

by:
Harry Lee earned 350 total points
ID: 39717826
Seamus2626,

The following code should do the job for you. I'm assuming the FE and FF filter value changes from time to time, and have setup input box for you.

Please let me know if those doesn't change. I can hard code them in for you.

Sub DeleteFEandFFRows()
Dim WS As Worksheet, FltRng As Range, FESrcStr As String, FFSrcStr As String, DelFltRng As Range
Set WS = ActiveSheet
Set FltRng = Range(Cells(1, 1), Cells(WS.Cells(Rows.Count, 1).End(xlUp).Row, WS.Cells(1, Columns.Count).End(xlToLeft).Column))
Set DelFltRng = Range(Cells(2, 1), Cells(WS.Cells(Rows.Count, 1).End(xlUp).Row, WS.Cells(1, Columns.Count).End(xlToLeft).Column))

FESrcStr = InputBox("Please Enter Search String for Column FE")
    If IsNull(FESrcStr) Then
        MsgBox ("Invalid Search String Is Entered")
        Exit Sub
    End If
FFSrcStr = InputBox("Please Enter Search String for Column FF")
    If IsNull(FFSrcStr) Then
        MsgBox ("Invalid Search String Is Entered")
        Exit Sub
    End If

With FltRng
    .AutoFilter
    .AutoFilter Field:=161, Criteria1:=FESrcStr
    .AutoFilter Field:=162, Criteria1:=FFSrcStr
    DelFltRng.Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.EntireRow.Delete Shift:=xlUp
    .AutoFilter
End With

WS.Cells(1, 1).Select

End Sub

Open in new window

0
 
LVL 10

Expert Comment

by:broro183
ID: 39718389
hi,

Not for points.

Here's a slightly modified version of Harry's code with a check that there are some visible rows after the filter is applied. I've removed the ".select" lines, which relate to the rows being deleted, because it may make the code slightly faster too.

Option Explicit

Sub DeleteFEandFFRows_v2()
Dim WS As Worksheet, FltRng As Range, FESrcStr As String, FFSrcStr As String, DelFltRng As Range
Dim VisRows As Range    'visible rows

    Set WS = ActiveSheet
    With WS
        Set FltRng = .Range(.Cells(1, 1), .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, .Cells(1, .Columns.Count).End(xlToLeft).Column))
        Set DelFltRng = .Range(.Cells(2, 1), .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, .Cells(1, .Columns.Count).End(xlToLeft).Column))
    End With

    FESrcStr = InputBox("Please Enter Search String for Column FE")
    If IsNull(FESrcStr) Then
        MsgBox ("Invalid Search String Is Entered")
        Exit Sub
    End If
    FFSrcStr = InputBox("Please Enter Search String for Column FF")
    If IsNull(FFSrcStr) Then
        MsgBox ("Invalid Search String Is Entered")
        Exit Sub
    End If

    With FltRng
        .AutoFilter
        .AutoFilter Field:=161, Criteria1:=FESrcStr
        .AutoFilter Field:=162, Criteria1:=FFSrcStr
        'set a range variable to the visible rows after applying the filter, test that some rows exist & delete them
        Set VisRows = DelFltRng.SpecialCells(xlCellTypeVisible).EntireRow
        If Not VisRows Is Nothing Then
            VisRows.Delete Shift:=xlUp
        End If
        .AutoFilter
    End With

    WS.Cells(1, 1).Select

Set VisRows = nothing
End Sub

Open in new window


hth
Rob
0
 

Author Comment

by:Seamus2626
ID: 39720978
Hi guys,

all looks good, but can i have the code without the search boxes, so it the code just runs without any user interaction

Thanks
0
 
LVL 10

Assisted Solution

by:broro183
broro183 earned 150 total points
ID: 39720984
hi Seamus,

I'm a great believer in "teaching a man to fish". Have you tried to make this small modification yourself?

I'll give you a hint - FESrcStr & FFSrcStr are String variables (ie text) & you can decide what is assigned as a value to string variables.

Rob
0
 

Author Closing Comment

by:Seamus2626
ID: 39720989
Yep, perfect, that makes sense Rob.

Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question