Solved

Filter and Delete

Posted on 2013-12-13
8
172 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now