Results 1 to 6 of 6

when removing filter all data disappears

Threaded View

rinser when removing filter all data... 12-07-2009, 11:48 AM
StephenR re: when removing filter all... 12-07-2009, 12:28 PM
rinser Re: when removing filter all... 12-07-2009, 01:51 PM
rinser Re: when removing filter all... 12-11-2009, 05:13 PM
rinser Re: when removing filter all... 12-17-2009, 06:42 AM
cartedor Re: when removing filter all... 01-15-2015, 06:09 PM
  1. #1
    Forum Contributor rinser's Avatar
    Join Date
    02-27-2009
    Location
    Bucharest
    MS-Off Ver
    MS Office 2013
    Posts
    103

    when removing filter all data disappears

    Hi All,

    I have a filter in my code that is supposed to filter data by a certain criteria, delete the filtered results and afterwards eliminate filter so that remaining rows would be seen by the next module.
    The thing is it does filter correctly, but then if there is no data to delete (no data meets filter criteria), after removing the filter there is actually no data at all - it deletes the rows hidden by filter, it seems. If there is data that meets criteria and should be deleted then it deletes it and after removing the filter the data is ok. The problem arises when there is nothing to delete, it just deletes the hidden (by filter) rows.
    Here is the code (the filter part is in bold):
    Lastrow = Range("A" & Rows.Count).End(xlUp).Row
        Rows("1:1").Font.Bold = True
        
        Range("L2:L" & Lastrow).FormulaR1C1 = "=DATEVALUE(RC2)"
        
        Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Select
        Selection.Sort Key1:=Range("A2"), Key2:=Range("L2"), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
        
        
        Range("J2:J" & Lastrow).FormulaR1C1 = "=COUNTIF(RC[-9]:R[+1]C[-9],RC[-9])"
        Range("K2:K" & Lastrow).FormulaR1C1 = "=R[-1]C+1"
        Range("K2:K" & Lastrow).Value = Range("K2:K" & Lastrow).Value
            
        
        Range("F1").AutoFilter
        Range("F1").AutoFilter Field:=10, Criteria1:=">1", Operator:=xlAnd
        Rows("2:" & Lastrow).Select
        Selection.Delete Shift:=xlUp
        Range("J1").AutoFilter    
        Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Select
        Selection.Sort Key1:=Range("K2"), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
        Columns("K:L").ClearContents
    This is a sample of the data:
    Sample sorting.xls

    Please advise me as to what is wrong with this code or how can it be improved. Any ideas appreciated.
    Last edited by rinser; 12-17-2009 at 07:40 AM.

Thread Information

Users Browsing this Thread

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

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