Autofil on variable rows, delete extract and show remaining rows
 

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

Autofil on variable rows, delete extract and show remaining rows
 

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





On 16 Nov, 19:42, 1plane wrote:
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

Autofil on variable rows, delete extract and show remaining rows
 

On Nov 16, 8:36*pm, AB wrote:
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

On 16 Nov, 19:42, 1plane wrote:



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- Hide quoted text -

- 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