Solved

Filter and Delete

Posted on 2013-12-13
8
174 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
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…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

825 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