Autofil on variable rows, delete extract and show remaining rows

[1plane]

Dear All,

I would like to automate the code below:
Sub Macro2()
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="<=08/03/2004",
Operator:=xlAnd
Rows("12:12").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=3
End Sub


The problem is that when I use the editor to record the macro, the
range is fixed because I click the cell to autofil. I will have varing
amount of rows and hence the need to automate a code to recognise
this.

Below is a sample of what I am trying to do and the data.

(1) Insert Autofilter in header row 1
(2) Column B custom to less than or equal to 08/03/2004
(3) Delete entire result with variable autofil of rows upto
08/03/2004
(4) View remaining data over 08/03/2004
(5) Remove Autofilter


Invoice Invoice Date Date Paid Amount
PKR41 03/11/2003 08/06/2004 1222.81
11803 16/01/2004 17/05/2004 1045.75
011847 13/02/2004 17/05/2004 199.75
011881 27/02/2004 21/07/2004 199.75
011882 27/02/2004 21/07/2004 199.75
011888 02/03/2004 08/06/2004 504.07
11886 02/03/2004 19/07/2004 6835.42
11901 08/03/2004 08/06/2004 26502.12
11909 10/03/2004 28/05/2004 9165
011918 15/03/2004 17/05/2004 199.75
016996 03/03/2009 03/04/2009 2162
016997 03/03/2009 03/04/2009 3979
017002 04/03/2009 03/04/2009 3588
017034 12/03/2009 03/04/2009 6171.49
017036 13/03/2009 14/04/2009 1403
017037 13/03/2009 14/04/2009 3812.25
017039 13/03/2009 15/04/2009 97750
017038 13/03/2009 14/04/2009 97750
017052 23/03/2009 21/04/2009 290.95

Kind Regards

1plane

 

[AB]

If you only need to achieve what you describe - i.e., to get rid of
all the rows <=08/03/2004 in B then i don't think you need to use
filter.
If getting rid of the rows is the only purpose then you wouldn't need
a macro for that to begin with - you could just sort by column B and
delete manually all that you don't want to retain. (you can use a
helper sort column to revert to the initial sort order if needs be).
On the other hand, if you MUST use a macro this might be the way:

Sub DeleteMyRows()

Dim MaxDate As Date
Dim ndxR As Long
MaxDate = #8/3/2004#' Your max date here (hardcoded)

For ndxR = Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1
With Cells(ndxR, "B")
If .Value <= MaxDate Then .EntireRow.Delete
End With
Next ndxR

End Sub

 

[1plane]

If you only need to achieve what you describe - i.e., to get rid of
all the rows <=08/03/2004 in B then i don't think you need to use
filter.
If getting rid of the rows is the only purpose then you wouldn't need
a macro for that to begin with - you could just sort by column B and
delete manually all that you don't want to retain. (you can use a
helper sort column to revert to the initial sort order if needs be).
On the other hand, if you MUST use a macro this might be the way:

Sub DeleteMyRows()

    Dim MaxDate As Date
    Dim ndxR As Long
    MaxDate = #8/3/2004#' Your max date here (hardcoded)

    For ndxR = Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1
        With Cells(ndxR, "B")
            If .Value <= MaxDate Then .EntireRow.Delete
        End With
    Next ndxR

End Sub










- Show quoted text -

AB,

That works perfectly.

I needed it coded as it is part of an automated process.

Thank you for your assistance

1plane

 

[AB]

Welcome!
Thanks for the feedback!

A.

AB,

That works perfectly.

I needed it coded as it is part of an automated process.

Thank you for your assistance

1plane- Hide quoted text -

- Show quoted text -