Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

Filter and Delete

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
Seamus2626
Asked:
Seamus2626
2 Solutions
 
regmigrantCommented:
Does it have to be code? you could just use autofilter and Delete row?
Reg
0
 
Seamus2626Author Commented:
Yes, i need this to form part of a macro which regularly does the task

Thanks
0
 
5teveoCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Harry LeeCommented:
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
 
broro183Commented:
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
 
Seamus2626Author Commented:
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
 
broro183Commented:
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
 
Seamus2626Author Commented:
Yep, perfect, that makes sense Rob.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now