Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Filter and Delete

Posted on 2013-12-13
8
Medium Priority
?
180 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 12

Accepted Solution

by:
Harry Lee earned 1400 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 600 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

916 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