+ Reply to Thread
Results 1 to 6 of 6

Macro to delete all rows not filtered

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    07-11-2010
    Location
    Minneapolis, USA
    MS-Off Ver
    Excel 2016
    Posts
    308

    Macro to delete all rows not filtered

    I have a macro that filters

    I need to delete all rows NOT filtered, I have not been able to find or come up with anything on how to do this

    Thanks

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Macro to delete all rows not filtered

    Maybe not what you ask, but it could be a workaround.

    Set the filter on the rows you want to delete.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Huntsville, AL
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Macro to delete all rows not filtered

    Range("A1").CurrentRegion.Select
            Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1, Selection.Columns.Count).Select
            Selection.EntireRow.Delete
    This is assuming that the data is all in a table format and that the table starts at Row 1. It also keeps the headers.

  4. #4
    Forum Contributor
    Join Date
    07-11-2010
    Location
    Minneapolis, USA
    MS-Off Ver
    Excel 2016
    Posts
    308

    Re: Macro to delete all rows not filtered

    Thank you greggetersen75, that was what I needed.

    I am filtering on all the duplicates and deleting the unique rows this is fast and simple way to do this.

    I am open to other suggestions if you have them.
    Last edited by capson; 05-06-2014 at 09:53 AM.

  5. #5
    Valued Forum Contributor
    Join Date
    01-19-2010
    Location
    Melbourne Australia
    MS-Off Ver
    latest is Excel 2016. have older versions
    Posts
    624

    Re: Macro to delete all rows not filtered

    @oeldere,

    That is thinking outside the square, what a really simple suggestion that I imagine would be simple to record/develop

    Well done

    Jmac

    Cheers

    Jmac1947

    1. Please consider clicking on the * Add Reputation if you think this post has helped you
    2. Mark your thread as SOLVED when question is resolved

  6. #6
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Huntsville, AL
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Macro to delete all rows not filtered

    Well I am all into reclyling code. So I often use sub routines over and over again and name ranges for fields, So for this example, let's say I have a worksheet called "Data", a field named "Project", and I am trying to delete a project called "T3-1Test"
    Sub DeleteRangeSample()
    
    Call RangeName(Worksheets("Data"))
    Call DeleteRange(Worksheets("Data"), "Project", "T3-1Test")
    
    End Sub
    Private Sub RangeName(xlWs As Excel.Worksheet)
        'Add Range Names for Reference
        Application.DisplayAlerts = False
        xlWs.Range("A1").CurrentRegion.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False   '########## changed
        Application.DisplayAlerts = True
           
    End Sub
    Private Sub DeleteRange(xlWs As Excel.Worksheet, strRangeName As String, varTypeNumText As Variant)
        Dim xlCell As Excel.Range
        With xlWs
        Set xlCell = .Range(strRangeName).Find(What:=varTypeNumText, LookIn:=xlValues, Lookat:=xlWhole)
            If Not xlCell Is Nothing Then
                If .AutoFilterMode = True Then .AutoFilterMode = False
                .Cells(1, 1).CurrentRegion.AutoFilter Field:=Range(strRangeName).Column, Criteria1:=varTypeNumText, Operator:=xlAnd
                .Range(strRangeName).EntireRow.Delete
                .AutoFilterMode = False
            End If
        End With
        
    End Sub
    This is a more sophisticated solution, but really reusable.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 10
    Last Post: 06-07-2018, 04:16 PM
  2. Replies: 1
    Last Post: 04-08-2014, 02:50 AM
  3. How to delete a filtered set of rows through macro
    By saidineshbabu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-02-2013, 08:33 AM
  4. Replies: 4
    Last Post: 06-11-2010, 03:29 PM
  5. Excel - How do I delete filtered out rows?
    By rook in forum Excel General
    Replies: 2
    Last Post: 11-30-2005, 06:50 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1